Re: Loading dates with nullif clause

From: <thezig_at_my-deja.com>
Date: Sun, 30 May 1999 05:55:36 GMT
Message-ID: <7iqjso$83m$1_at_nnrp1.deja.com>


In article <374ED86B.3C0D494_at_uni.edu>,   Kevan Forest <kevan.forest_at_uni.edu> wrote:
> I am trying to load a file that has dates in the format of MMDDYY.
Some
> of the date fields have '000000' in them rather than a valid date. In
> this case I want to load nulls to the field.
>
> The problem I am having is that when I give SQL*LOADER the date format
> first, and the null if second, the load blows up on my zero records
> because the date is invalid. If I list the null if first, Oracle will
> not recognize my format statement which follows and assumes its
default
> date format.
>
> examples taken from control file:
> YR_PACKAGE_DATE position(238:243) date "MMDDYY" nullif
> (238:243)="000000")
> The above runs, but tries to validate the data using the MMDDYY format
> before evaluating my nullif statement, thus causing the records to be
> thrown out.
>
> YR_PACKAGE_DATE position(238:243) nullif (238:243)
="000000"
> date "MMDDYY" )
> This bombs because oracle does not want the date "MMDDYY" in this
> location.
>
> If I remove the date "MMDDYY" statement all together Oracle throws out
> the non zero dates because they do not match the default Oracle
format.
>
> Does anyone have any ideas other than changing the format of my
existing
> file I am trying to load from?
>
> Kevan Forest
> Database Administrator
> University of Northern Iowa
> kevan.forest_at_uni.edu
>
>
Try this line instead.

YR_PACKAGE_DATE position(238:243) date "MMDDYY" nullif (YR_PACKAGE_DATE = "000000")

The example in the book doesn't show the nullif containing position values but the field name instead (I haven't tried this).

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun May 30 1999 - 07:55:36 CEST

Original text of this message