Loading dates with nullif clause

From: Kevan Forest <kevan.forest_at_uni.edu>
Date: Fri, 28 May 1999 12:54:51 -0500
Message-ID: <374ED86B.3C0D494_at_uni.edu>

[Quoted] I am trying to load a file that has dates in the format of MMDDYY. Some [Quoted] of the date fields have '000000' in them rather than a valid date. In this case I want to load nulls to the field.

[Quoted] 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" )
[Quoted] 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.

[Quoted] Does anyone have any ideas other than changing the format of my existing [Quoted] file I am trying to load from?

Kevan Forest
Database Administrator
University of Northern Iowa
kevan.forest_at_uni.edu Received on Fri May 28 1999 - 19:54:51 CEST

Original text of this message