Stack Exchange
Sql loader conditions
Can I do something like this in SQL LOADER?
LOAD DATA
INFILE 'table_with_one_million_rows.dat'
INTO TABLE TABLE_WITH_ONE_MILLION_ROWS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
   COL1                     DECIMAL 
 , COL2                     DECIMAL IF COL1='X' '1' else '0'
 , COL3                     CHAR 

)


A condition in SQL*Loader can be performed using decode.
Using a DECIMAL in your example will get the error:
SQL*Loader-402: Unable to determine length of column COL1 from specification

Suggest using DECIMAL EXTERNAL instead:
LOAD DATA
INFILE 'table_with_one_million_rows.dat'
INTO TABLE TABLE_WITH_ONE_MILLION_ROWS
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(
   COL1                     DECIMAL EXTERNAL
 , COL2                     DECIMAL EXTERNAL "decode(:COL2, 'X', '1', '0')"
 , COL3                     CHAR 

)


Great, thanks a lot. And another thing if for example I use: decode(:col1,'X','1',decode(:col0,'A','0','-1')) Is this OK?

You can't use :col0 as it's not defined. However, if it was defined then, yes, this is allowable. It will set COL2 to '1' if COL1 is 'X' else it will set COL2 to 'A' if COL0 is '0' else it will set COL2 to '-1'. If you meant to use :col1 instead of :col0 then it can be simplified to decode(:col1,'X','1','A','0','-1')

No, I meant to take into consideration both :COL1 and :COL0 to evaluate :COL2.  Something like If elseif else . I`ll give it a try anyway..thanks

It does not work.. could u please tell me if my syntax is wrong or im trying to do sth impossible:                                             code` C1 POSITION (1:3),                                                        C2 POSITION (4:6),                                                     C3 "DECODE(:C1,'uno',DECODE(:C2,''due'',''Y'',''N''),'N')"
