RE: help in SqlLoader; NULLIF function

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 09 May 2001 14:15:21 -0700
Message-ID: <F001.002FE797.20010509135535@fatcity.com>

see answer below

> -----Original Message-----
> From: Venkatesh, Sankar (CAP, GEFA)
> [mailto:Sankar.Venkatesh_at_gecapital.com]
>
> 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:

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 Received on Wed May 09 2001 - 16:15:21 CDT