2

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 

)
John Doyle
  • 7,475
  • 5
  • 33
  • 40
Samson
  • 2,801
  • 7
  • 37
  • 55

1 Answers1

6

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 

)
John Doyle
  • 7,475
  • 5
  • 33
  • 40
  • Great, thanks a lot. And another thing if for example I use: decode(:col1,'X','1',decode(:col0,'A','0','-1')) Is this OK? – Samson Jan 19 '12 at 16:21
  • 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')` – John Doyle Jan 20 '12 at 00:04
  • 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 – Samson Jan 20 '12 at 07:31
  • It does not work.. could u please tell me if my syntax is wrong or i`m trying to do sth impossible: `code` C1 POSITION (1:3), C2 POSITION (4:6), C3 "DECODE(:C1,'uno',DECODE(:C2,''due'',''Y'',''N''),'N')" – Samson Jan 20 '12 at 08:20