see answer below
> -----Original Message-----
> From: Venkatesh, Sankar (CAP, GEFA)
> [mailto:[EMAIL PROTECTED]]
>
> Using Solaris and Oracle 8.1.7
>
> What is the syntax of NULLIF condition in "sqlldr ", when we
> have multiple
> null conditions for a column. I could not find the syntax for multiple
> NULLIF conditions on "Oracle8i_Utilities" manual .
>
> I want to feed the column with NULL when the input data for the column
> TerminationDate (Data datatype ) is "000000" or "999999" or
> "731014" . The
> input data being in the form "YYMMDD"
> I don't have problem running single NULLIF condition as in
> EffectiveDate
>
>
> LOAD DATA
> INTO TABLE AWW.Dealer
> (
> Dealer POSITION(01:08) CHAR(8) ,
> EffectiveDate POSITION(09:14) DATE(6) "YYMMDD"
> NULLIF(EffectiveDate = "000000") ,
> .
> .
> .
> StmtSeq POSITION(56:57) CHAR(2) ,
> TerminationDate POSITION(58:63) DATE(6) "YYMMDD"
> NULLIF(TerminationDate = "000000" OR TerminationDate = "999999" OR
> TerminationDate = "731014")
> )
Use a decode for the date field.
Example:
Table T has one column, d, of type date
input file:
2002/03/04
1992/01/01
1961/08/21
2001/06/13
1950/12/31
ctl file
load data
infile 'date_test.txt'
badfile 'date_test.bad'
discardfile 'date_test.dsc'
into table t
( d position (1:10)
"decode (:d, '1992/01/01', null, '2001/06/13', null, to_date (:d, 'YYYY/MM/DD'))"
)
after sql*load:
SQL> set null "NULL DATE"
SQL> select to_char (d, 'YYYY/MM/DD') from t ;
TO_CHAR(D,
----------
2002/03/04
NULL DATE
1961/08/21
NULL DATE
1950/12/31
------
Jacques R. Kilchoer
(949) 754-8816
Quest Software, Inc.
8001 Irvine Center Drive
Irvine, California 92618
U.S.A.
http://www.quest.com
