SQL Loader, CTL file: How to default a value

raksrules

Elite
I have a CTL file as follows

Code:
LOAD DATA
APPEND
INTO TABLE IMPORT_DETAILS_TMP 
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( CLIENTNO	INTEGER EXTERNAL   
 ,SITENO	INTEGER EXTERNAL  
 ,CUSTLEV3ID
 ,CUSTLEV4ID
 ,PERIOD_END	DATE "MMDDYYYY"
 ,dummy1 FILLER
 ,CUSTLEV5ID 
 ,col8 FILLER
 ,col9 FILLER
 ,col10 FILLER
 ,col11 FILLER
 ,col12 FILLER
 ,col13 FILLER
 ,col14 FILLER
 ,dummy2 FILLER
 ,dummy4 FILLER
 )

Now here i want to add an additional column named SERVICEID which is something i do not receive in the data file. But i want to default some value (say 0) and insert the same using this control file in the table.
Effectively i want to control file as follows

Code:
LOAD DATA
APPEND
INTO TABLE IMPORT_DETAILS_TMP 
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
( CLIENTNO	INTEGER EXTERNAL   
 ,SITENO	INTEGER EXTERNAL  
 ,CUSTLEV3ID
 ,CUSTLEV4ID
 ,PERIOD_END	DATE "MMDDYYYY"
 ,dummy1 FILLER
 ,CUSTLEV5ID 
 ,col8 FILLER
 ,col9 FILLER
 ,col10 FILLER
 ,col11 FILLER
 ,col12 FILLER
 ,col13 FILLER
 ,col14 FILLER
 ,dummy2 FILLER
 ,dummy4 FILLER
 ,SERVICEID	DEFAULT 0
 )

Now when i run the sql loader command on this ctl file it throws an error as follows

Code:
SQL*Loader-350: Syntax error at line 22.
Expecting "," or ")", found "DEFAULT".
 ,SERVICEID     DEFAULT 0

How do i specify this DEFAULT for a column in the table IMPORT_DETAILS_TMP and populate the same without the corresponding data not being available in the data file
 
Code:
LOAD DATA

APPEND

INTO TABLE IMPORT_DETAILS_TMP 

FIELDS TERMINATED BY "|"

TRAILING NULLCOLS

( CLIENTNO	INTEGER EXTERNAL   

 ,SITENO	INTEGER EXTERNAL  

 ,CUSTLEV3ID

 ,CUSTLEV4ID

 ,PERIOD_END	DATE "MMDDYYYY"

 ,dummy1 FILLER

 ,CUSTLEV5ID 

 ,col8 FILLER

 ,col9 FILLER

 ,col10 FILLER

 ,col11 FILLER

 ,col12 FILLER

 ,col13 FILLER

 ,col14 FILLER

 ,dummy2 FILLER

 ,dummy4 FILLER

[B] ,SERVICEID DECODE(:SERVICEID, NULL, GetDefaultData('Default', 'SERVICEID'), :SERVICEID)[/B]

 )

Hope it helps you.
 
Back
Top