IMP-00058, ORA-00904, Invalid column name

OS= Win NT
Oracle ver= 8.1.6.1.0

Howdy boys and girls. I have a weird error when trying to do an import from the same database. I exported the data and changed the table column names, not the types but the names and now I am getting this error. The .DMP file came from the same tables.
Basically I added a few columns and changed the QTY to QY on some column names. I did this by dropping and re-creating the table.
Suggestions??

BEFORE
Name Null? Type
------------------------------- -------- ----
MARKET_ID NOT NULL NUMBER(10)
PQS_SEG_START_DATE DATE
PQ_ID NOT NULL VARCHAR2(10)
PQS_ID NOT NULL VARCHAR2(8)
PROD_GRP_TYPE_CD NOT NULL CHAR(4)
PQS_SEG_ID NOT NULL CHAR(2)
MKT_SEG_CD NOT NULL CHAR(3)
BASIS_TYPE_CD NOT NULL CHAR(1)
PERIOD_TYPE_CD NOT NULL CHAR(3)
PRICE_DRV_MIN_QTY NOT NULL NUMBER(18,4)
PROD_ID NOT NULL NUMBER(10)
MKT_SEG_EFF_DATE NOT NULL DATE
PROD_OFFER_EFF_DATE NOT NULL DATE
ZONE NOT NULL CHAR(3)
BILLING_OPTION_TYPE_CD NOT NULL CHAR(2)
ACCESSORY_IR NOT NULL CHAR(1)
PROD_CD CHAR(4)
PROD_ATTR_1_ID NUMBER(10)
PROD_ATTR_2_ID NUMBER(10)
PROD_ATTR_3_ID NUMBER(10)
PROD_ATTR_4_ID NUMBER(10)
PROD_ATTR_5_ID NUMBER(10)
PROD_ATTR_6_ID NUMBER(10)
PRICE_DRV_MAX_QTY NUMBER(18,4)
MEASURE_UNIT_CD CHAR(2)
CURRENCY_CD CHAR(3)
AMOUNT NUMBER(15,2)
PRICE_TYPE_CD CHAR(1)
PRICE_MEASURE_UNIT_CD CHAR(2)
AMOUNT_FLOOR NUMBER(15,2)
AMOUNT_CEILING NUMBER(15,2)
PG_PLAN_ID VARCHAR2(40)
AMOUNT_APPROVED NUMBER(15,2)
AMOUNT_REQ NUMBER(15,2)
AMOUNT_EQUIV NUMBER(15,2)
PQS_TIER_ID NUMBER(9)
REIMB_MTH_TYP_CD CHAR(1)
ACT_ROLLUP_IR CHAR(1)
ROLLUP_PRD_QY NUMBER(5)
WK_AVG_INCL_CURR_WK_IR CHAR(1)
USE_HIST_IR CHAR(1)
TIER_TYP_CD CHAR(1)
PG_PLAN_TYPE_CD CHAR(1)
AMOUNT_REC NUMBER(15,2)
AMOUNT_MAX NUMBER(15,2)
AMOUNT_1 NUMBER(18,4)
AMOUNT_2 NUMBER(18,4)
AMOUNT_3 NUMBER(18,4)
PRICE_PLAN_ID NUMBER(10)
AMOUNT_EQUIV_APPROVED NUMBER(15,2)
LNE_NR NOT NULL VARCHAR2(10)
NCV_MIN_RPP_HIN_CD NOT NULL CHAR(2)


AFTER...

Name Null? Type
------------------------------- -------- ----
MARKET_ID NOT NULL NUMBER(10)
PQS_SEG_START_DATE DATE
PQ_ID NOT NULL VARCHAR2(10)
PQS_ID NOT NULL VARCHAR2(8)
PROD_GRP_TYPE_CD NOT NULL CHAR(4)
PQS_SEG_ID NOT NULL CHAR(2)
MKT_SEG_CD NOT NULL CHAR(3)
BASIS_TYPE_CD NOT NULL CHAR(1)
PERIOD_TYPE_CD NOT NULL CHAR(3)
PRICE_DRV_MIN_QY NOT NULL NUMBER(18,4)
PROD_ID NOT NULL NUMBER(10)
MKT_SEG_EFF_DATE NOT NULL DATE
PROD_OFFER_EFF_DATE NOT NULL DATE
ZONE NOT NULL CHAR(3)
BILLING_OPTION_TYPE_CD NOT NULL CHAR(2)
ACCESSORY_IR NOT NULL CHAR(1)
PROD_CD CHAR(4)
PROD_ATTR_1_ID NUMBER(10)
PROD_ATTR_2_ID NUMBER(10)
PROD_ATTR_3_ID NUMBER(10)
PROD_ATTR_4_ID NUMBER(10)
PROD_ATTR_5_ID NUMBER(10)
PROD_ATTR_6_ID NUMBER(10)
PRICE_DRV_MAX_QY NUMBER(18,4)
MEASURE_UNIT_CD CHAR(2)
CURRENCY_CD CHAR(3)
AMOUNT NUMBER(15,2)
PRICE_TYPE_CD CHAR(1)
PRICE_MEASURE_UNIT_CD CHAR(2)
AMOUNT_FLOOR NUMBER(15,2)
AMOUNT_CEILING NUMBER(15,2)
PG_PLAN_ID VARCHAR2(40)
AMOUNT_APPROVED NUMBER(15,2)
AMOUNT_REQ NUMBER(15,2)
AMOUNT_EQUIV NUMBER(15,2)
PQS_TIER_ID NUMBER(9)
REIMB_MTH_TYP_CD CHAR(1)
ACT_ROLLUP_IR CHAR(1)
ROLLUP_PRD_QY NUMBER(5)
WK_AVG_INCL_CURR_WK_IR CHAR(1)
USE_HIST_IR CHAR(1)
TIER_TYP_CD CHAR(1)
PG_PLAN_TYPE_CD CHAR(1)
AMOUNT_REC NUMBER(15,2)
AMOUNT_MAX NUMBER(15,2)
AMOUNT_1 NUMBER(18,4)
AMOUNT_2 NUMBER(18,4)
AMOUNT_3 NUMBER(18,4)
PRICE_PLAN_ID NUMBER(10)
AMOUNT_EQUIV_APPROVED NUMBER(15,2)
LNE_NR NOT NULL VARCHAR2(10)
NCV_MIN_RPP_HIN_CD NOT NULL CHAR(2)
NCV_MAX_APV_DCT_PR NUMBER(15,2)
NCV_MAX_APE_DCT_PR NUMBER(15,2)




ORA-00904 invalid column name
Sorry the table is so long, is this a known bug?

Thanks in advance again,

MH

You can't do that. The column name must be the same. You shall do:

1. Recreate the original table
2. Import this table
3. Create the new table
4. Do: insert into new_table(column1,column2 ...) select cloumn1,clomn2 ... from old_table
5. Drop the old table
6. Rename new_table to old_table

Hope that helps

Angel

what exactly r u trying to do. u've taken the export and then changed your table structure. so you r trying to import the tables after dropping from the database or just importing the data into existing table.

??????

Quote:

---

Originally posted by aarroyob
You can't do that. The column name must be the same. You shall do:

1. Recreate the original table
2. Import this table
3. Create the new table
4. Do: insert into new_table(column1,column2 ...) select cloumn1,clomn2 ... from old_table
5. Drop the old table
6. Rename new_table to old_table

Hope that helps

Angel

---

Thanks guys!!

That is exactly what I though arroyob but I was told that you can change the column names as long as the datatypes are the same, I disgreed.

Basically we needed to add two columns and re-name some of the columns that had the name BLAH_BLAH_QTY
to BLAH_BLAH_QY to conform to business standards. I just found out that this was incorrect and that I will NOT have to re-name the columns, thanks god. All I need to do now is ALTER ADD, much easier.

Thanks again guys.

MH

You can add columns to a table and import back into them but you can not rename a column and import into it again.

The reason for this is when you do an export it exports the data in the form insert into table_name ( all column names) values (data). When the import tries to do these inserts it finds a problem with the column name because they do not match. Another issue is you can not do an import into a table that has had a not null column added to it with no default value. The column needs to be populated by the inserts