FW: help in SqlLoader; NULLIF function

Thanks Jacques

Your suggestion worked perfectly for me.

The following is the exact translation in my case (ditto of your suggestion).

TerminationDate POSITION(58:63) "decode (:TerminationDate, '000000', NULL, '999999', NULL, '731014', NULL, to_date (:TerminationDate, 'YYMMDD') )" , Conventional load works fine . Direct loan is not allowed in this case. It will take longer time to load but it works.

Thanks a bunch Jacques.

Regards

> g GE CAPITAL
>
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> --------------------------------------------------------------------------
> ----------

Sankar Venkatesh
GE Financial Assurance /Auto Warranty Services Dial Com:8*353-4242 ; 303-987-4242

> -----Original Message-----
> From: Jacques Kilchoer [SMTP:Jacques.Kilchoer_at_quest.com]
> Sent: Wednesday, May 09, 2001 3:56 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: help in SqlLoader; NULLIF function
>
> 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:
> 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>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Venkatesh, Sankar (CAP, GEFA)
  INET: Sankar.Venkatesh_at_gecapital.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).