Re: SQLLoader and compensating for incorrect date values

dotacion_at_excite.com (Richard Drive) wrote in message news:<78586940.0111211143.7f68bab5_at_posting.google.com>...
> I am using SQLLDR to load data from a fixed length file using this ctl
> file:
> LOAD DATA
>
> -- INFILE D:\jul1501.txt
> INSERT INTO TABLE CR51.CR_PEOPLE
> TRAILING NULLCOLS
> ( CR_ID DECIMAL EXTERNAL "CR51.CR_PEOPLE_ID_S.NEXTVAL",
> FILE_ID position (1:9) char,
> LAST_NAME position (10:24) char,
> FIRST_NAME position (25:33) char,
> MI position (34:34) char,
> ADDRESS position (35:54) char,
> CITY position (55:69) char,
> BIRTH_DATE position (70:74) DATE "MMDDYYYY" NULLIF (BIRTH_DATE
> = "not a valid month"),
> CREATION_DATE SYSDATE
> )
>
> but which won't work of course because it is evaluated
> in the data source, occasionally some of the date columns have invalid
> dates- not lways null, just bad or incorrect data, e.g.:
>
> 772660018SMITH SUSAN 691 N ROVER ROAD SAN JOSE 06311952
> There is no 6/31/1952
> 772660018SMITH GEOFF 691 N ROVER ROAD SAN JOSE 1952
> The data is invalid, year only
> 772660018SMITH SUSAN 691 N ROVER ROAD SAN JOSE
> It's completely missing
>
> I would prefer to just load these columns as null, but I'm not finding
> the correct syntax.
>
> Any comments or suggestions?
>
> I'd prefer not to edit the bad file - there are too many records.

I think you can create a user-defined function which have a string as parameter and return 'Yes' if the string is a valid date, or 'No' if the string is an invalid date. Then, you can use this function in you NULLIF clause, that is, NULLIF(func(BIRTH_DATE)= 'No'). I am not sure if it will work. But you can try it. Received on Thu Nov 22 2001 - 01:58:56 CET