Home » RDBMS Server » Server Utilities » Control File Problem
| Control File Problem [message #368185] |
Tue, 26 September 2000 10:33  |
Mark Wolov
Messages: 2
Registered: September 2000
|
Junior Member |
|
|
I am having a problem with a control file I have modified. It is loading large comma delimited files to several tables dependent on when clauses (it used to be to just one table). The problem is that no rows will load to the new tables I have created. When I check the log it says the problem is due to the When clause failing. I know that the data is there and my statements are valid, but it still won't load! Here is a sample of my code:
LOAD DATA
INTO TABLE LOAD_X
WHEN (BUY_TYPE = 'X')
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(EVENT,
IP,
EVENT_DATE DATE "MM-DD-YYYY" TERMINATED BY ' ',
EVENT_TIME,
ADVERTISER,
BUY_TYPE,
IO,
RATE,
DATE_RANGE,
AD_SIZE,
PRIORITY,
REDIRECT,
ID,
RICH_MEDIA,
TARGET,
SITE_CODE,
CREATIVE_NAME,
SITE_PAGE NULLIF SITE_PAGE=BLANKS)
I've tried lots of things to get this to load including putting POSITION(1) after the first column name, but nothing seems to work. Any suggestions would be greatly appreciated!
Mark
|
|
|
|
| Re: Control File Problem [message #368187 is a reply to message #368185] |
Wed, 27 September 2000 02:58  |
Kieron Smythe
Messages: 11
Registered: March 2000
|
Junior Member |
|
|
Hi Mark,
Take a look at the following CTL file definition. Here I split into the same table but different definitions depending on the record type.
LOAD DATA
INFILE *
APPEND INTO TABLE MOTO_TEMP_PICK
WHEN (RECORD_TYPE = 'MPNHDR')
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
TEMP_REC_ID SEQUENCE(MAX,1),
RECORD_TYPE POSITION(1) ,
TP_CODE POSITION(*) ,
SITE_ID POSITION(*) ,
"SYSTEM_DATE" POSITION(*) DATE "YYYYMMDD"
)
INTO TABLE MOTO_TEMP_PICK APPEND
WHEN (1:6 = 'MPNDTL')
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
TEMP_REC_ID SEQUENCE(MAX,1),
RECORD_TYPE POSITION(1) ,
SITE_ID POSITION(*) ,
MPN_ORDER_NUM POSITION(*) ,
MPN_ORDER_LINE_NUM POSITION(*) ,
PICK_LOC POSITION(*) ,
SUB_INV_LOC POSITION(*) ,
manufacturing_line POSITION(*) ,
MOTOROLA_ITEM_NUMBER POSITION(*),
REVISION_NUM POSITION(*) ,
DATE_REQ_BY POSITION(*) DATE "YYYYMMDD",
TIME_REQ_BY POSITION(*) ,
QUANTITY POSITION(*) ,
ORACLE_VENDOR_NUM POSITION(*) ,
ORACLE_VENDOR_NAME POSITION(*)
)
INTO TABLE MOTO_TEMP_PICK APPEND
WHEN (1:6 = 'MPNSHD')
FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY "'"
TRAILING NULLCOLS
(
TEMP_REC_ID SEQUENCE(MAX,1),
RECORD_TYPE POSITION(1) ,
SITE_ID POSITION(*) ,
MPN_ORDER_NUM POSITION(*) ,
MOTOROLA_ITEM_NUMBER POSITION(*),
QUANTITY POSITION(*) ,
SUB_INV_LOC POSITION(*) ,
DATE_REQ_BY POSITION(*) DATE "YYYYMMDD",
TIME_REQ_BY POSITION(*)
)
Notice the 1:6 in the when clause. I think if you add that in it will solve your problem.
You won't find that one in any manual. Had to get Oracle support to help me out.
Hope it helps.
Kieron
|
|
|
|
| Re: Control File Problem [message #368188 is a reply to message #368185] |
Wed, 27 September 2000 05:42 |
Chella
Messages: 25
Registered: September 2000
|
Junior Member |
|
|
In the WHEN clause, you have to use the position and not the actual field name. For example, if your field BUY_TYPE is starting at position 11, your WHEN clause will look something like this;
WHEN (11) = 'X'
Hope this helped.
|
|
|
|
| Re: Control File Problem [message #368189 is a reply to message #368185] |
Wed, 27 September 2000 05:42 |
Chella
Messages: 25
Registered: September 2000
|
Junior Member |
|
|
In the WHEN clause, you have to use the position and not the actual field name. For example, if your field BUY_TYPE is starting at position 11, your WHEN clause will look something like this;
WHEN (11) = 'X'
Hope this helped.
|
|
|
|
Goto Forum:
Current Time: Thu Apr 18 22:58:51 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
