Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SQL Loader to append data in same table but using differnet WHEN clauses

941425Nov 16 2012 — edited Nov 23 2012
In my data file i have a header record and a detail record identified by Record_type = 1 and 2 respectively.
The Database table has all the columns to capture detail records but i want to capture jus one column of header record now also in my existing table. So i have added that column (DATA_DATE)in my table but how to capture that value ?
im writing my control file using two WHEN clauses, something like -

load data
into table t_bdn
append
when RECORD_TYPE = '2'
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
SEQUENCE_NO
, RECORD_TYPE
, DISTRIBUTOR_CODE
, SUPPLIER_CODE
, SUPPLIER_DISTRIBUTOR_CODE
, DISTRIBUTOR_SKU
, SUPPLIER_SKU
)

when RECORD_TYPE = '1'
FIELDS TERMINATED BY "|" TRAILING NULLCOLS
(
SEQUENCE_NO FILLER
, RECORD_TYPE FILLER
, CREATE_DATE FILLER
, DATA_DATE "NVL(to_date(:DATA_DATE, 'YYYY/MM/DD'),to_date('9999/12/31', 'YYYY/MM/DD'))"
)


im getting error " expecting INTO and foung WHEN RECORD_TYPE = '1' "

if i give iNTO second time it will append a new row altogether in my table but i want the same row to be updated with this DATA_DATE value coming from RECORD_TYPE =1 and header record has 4 delimited data text fields only and i am interested in fetching just the 4th column..
KIndly suggest what to do ?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 21 2012
Added on Nov 16 2012
2 comments
1,595 views