Re: Two NULLIF in SQL*Loader

From: Saar <sxmaxoz_at_us.oracle.com>
Date: Tue, 04 Aug 1998 17:58:32 -0700
Message-ID: <35C7AE37.C81A3096@us.oracle.com>

Luis Gregorio wrote:

> I have a problem.
> I'm trying to load a text file into a Oracle table, that's not the problem,
> the problem is that this table has a Date column and my file has some
> records with valid dates and same records with invalid dates. Is there a way
> of inserting the records that don't have a valid Date (assigning null to the
> Date Column)? How?

NULLIF...
> I've tried using NULLIF but I found two kinds of invalid dates, blank dates
> and '00000000'. How can I put 2 conditions in the NULLIF clause.
>

As you found out, nullif is not sophisticated, so is then WHEN clause (since you can'thave an OR operator in it, only AND) but the combination of the two solves your problem.

here is a sample control file:

load data
infile *
into table a append
when dt!=BLANKS
(

    dt char nullif (a='1')
)

into table a append
when dt=BLANKS
(

    dt char nullif (a=BLANKS)
)
BEGINDATA
1
2
1

In this solution you have two WHEN caluses in one controlfile, as we can see from the logfile:

Column DT is NULL if DT = 0X31(character '1') Column DT is NULL if DT = BLANKS

Good luck,

--

 __  _    _  __    _ _   _   _ ___  ______________________________
((  /\\  /\\ ||)  |\V/| /\\ /\\ >/  Principal Performance Engineer

_))//-\\//-\\||\ |||||//-\\\\//<_ Oracle Corporation Compaq SBU

////////////////// Drop x's in email (spam) //////////////////////

Received on Tue Aug 04 1998 - 19:58:32 CDT