FW: help in SqlLoader; NULLIF function

[Venkatesh, Sankar (CAP, GEFA)]

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:[EMAIL PROTECTED]]
> 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:[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> 
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Venkatesh, Sankar (CAP, GEFA)
  INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).