SQL*Loader : Changing values with DECODE

Folks,

I am trying to load the data using Fixed Format datafile. and changing the value for different columns based on other columns. Here is the control file ,datafile and table structure/data I have used.

SQL>!cat p.ctl

LOAD DATA
INFILE 'p.dat'
REPLACE
PRESERVE BLANKS
INTO TABLE d_test
( id position(1:4) char,
  batch_number position(5:10) char
"DECODE(:id,'AAAA',:batch_number,'CCCC',:batch_number,NULL)",   c_date position(11:16) date "YYMMDD" "DECODE(:batch_number, ' ', NULL, NULL, NULL, :c_date)"
)

SQL>desc d_test

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ID                                                 CHAR(4)
 BATCH_NUMBER                                       CHAR(6)
 C_DATE                                             DATE

SQL>!cat p.dat

AAAAXXXXXX021022
BBBB 021023
CCCCYYYYYY021024
DDDDZZZZZZ021023 SQL> select id, nvl(batch_number,'NULL') batch_num, decode(c_date, NULL,'NULL',c_date) CDATE

   from d_test;

ID BATCH_ CDATE
---- ------ -----------
AAAA XXXXXX 22-OCT-2002
BBBB NULL NULL
CCCC YYYYYY 24-OCT-2002
DDDD NULL 23-OCT-2002 Here , I am changing the value of batch_number based value of "ID" column and changing c_date based on batch number. As per the expression , I have in the control file, value for the C_DATE for id DDDD should have been "021023" rather than NULL. I observerd that for value of :batch_number in the decode expression for c_date column , it still picking up the Origional value of 'ZZZZZZ' instead of the modified value of NULL.

Do you know why? Shouldn't SQL*loader pickup the modified value rather than the origional value? Is there any workaround?

Thanks in advance for your quick resolution.

Mak Received on Thu Oct 24 2002 - 20:02:06 CDT