Hi All,
I have SQL loader file(CTL) which is used to insert data into Header and Line tables using WHEN clause.
LEVEL Supplier Name Supplier Site
Header TEST CORPORATION TEST3COM01remit
LEVEL Item Quantity
Line 3RDA003422 2
Line 3RDA003423 1
Line 3RDA003424 3
CTL file
Options(errors=10000,skip=1)
LOAD DATA
INFILE /u01/app/oracle/file1.csv
REPLACE
INTO TABLE XXC_HEADER
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
WHEN LEVEL1 = 'Header'
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
Supplier_Name CHAR NULLIF Supplier_Name = BLANKS "RTRIM(:Supplier_Name)",
Supplier_Site CHAR NULLIF Supplier_Site = BLANKS "RTRIM(:Supplier_Site)"
)
INTO TABLE XXC_LINE
fields terminated by ','
optionally enclosed by '"'
trailing nullcols
WHEN LEVEL1 = 'Line'
(
LEVEL1 CHAR NULLIF LEVEL1 = BLANKS "RTRIM(:LEVEL1)",
item CHAR NULLIF item = BLANKS "RTRIM(:item)",
Quantity
)
SQL*Loader-350: Syntax error at line 9.
Expecting "(", found keyword when.
WHEN LEVEL1 = 'Header'
^