Re: SQL*Loader : Changing values with DECODE

try:

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(:id,'AAAA',:c_date,'CCCC',:c_date,NULL)" )

I believe that :batch_number refers to the input value, not the SQL modified value. So you need to operate off of an input value test.

hth,

drew

On 24-Oct-2002, maks70_at_attbi.com (MAK) wrote:

> 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 Fri Oct 25 2002 - 05:53:38 CEST