SQL*Loader- null dates in data source are " / / "

[pdtt]

Hi,

The data source files I am trying to load have a " / / "
mask when the date column is null. Does anyone know how to deal with this. Is there an oracle function in 8.1.7 that can test for a valid date like the VB function IsDate()?

Thank you

 

[did02]

Hi,

You can use a decode in the control file to insert NULL :
decode(field,' / / ',NULL,to_date(field,DD/MM/YY'))

Hope it helps,
Rgds,
Did02

 

[pdtt]

Hi Did02,

Thanks for the reply. I have tried a few variations of
syntax and I am getting the same error whenever I try
to use an Oracle function. Here is a portion of my control file.
FOREIGN_CD Char,
LAST_VISIT DATE "MM/DD/YYYY" decode (LAST_VISIT,' / / ',NULL,to_date(LAST_VISIT, "MM/DD/YYYY&quot,
NO_VISIT Integer External,

I have tried the LAST_VISIT line without the date mask, withouth the DATE and date Mask and I still get the following error. What am I messing up with the syntax. my attempts at using Oracle functions in the control files always give an error.

SQL*Loader-350: Syntax error at line 24.
Expecting "," or &quot", found "decode".
LAST_VISIT DATE "MM/DD/YYYY" decode(LAST_VISIT,' / / ',NULL,t

Thanks

 

[did02]

Pdtt,

Try this in your control file:
LAST_VISIT DATE "MM/DD/YYYY" NULLIF reference_dt=' / / ',
or
LAST_VISIT CHAR "TO_DATE(DECODELAST_VISIT, ' / / ',NULL, :LAST_VISIT),'MM/DD/YYYY')" ,

Both solutions should be ok.
Rgds,
Did02

 

[mghafoori]

you should always put the function in " ". the Decode function should work if you put it in " "

 

[pdtt]

Thank you did02 the LAST_VISIT DATE "MM/DD/YYYY" NULLIF reference_dt=' / / ', worked well.