|SQL LOADER DATE NULLIF, BIG PROBLEM !! - Urgent need of help ! [message #224704]
||Thu, 15 March 2007 03:40
Registered: March 2007
Dear All, I would appriciate some help with this.|
I have this set of data
which is TAB delimited,
XX XXXXXXXXXXX XXXXXX XXXXXXXX X XX XX XXX (empty date) XXXX X.XX XX.XX XX.XXX XXXX XXXXXX XX.XXX dd/mm/yyyy X.XXXX XX XXXXXXX X.XXX XXX XXX
Now the problem is that where there is 00/00/0000 t(I PUT THAT THERE) its a null value, its just empty space, I am attaching the control file
INFILE 'X:\$\$\$$$$.csv' BADFILE 'X:\$\$\$$$$.bad'
INTO TABLE testtable.TABLE_NAME
FIELDS TERMINATED BY " " optionally enclosed by '"'
FIELD_3 DATE 'dd/mm/yyy' NULLIF FIELD_3='',
I also tried these
FIELD_3 "TO_DATE(:FIELD_3,'dd/mm/yyyy')" NULLIF(:FIELD_3=BLANKS)
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3=BLANKS,
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3="",
FIELD_3 DATE 'dd/mm/yyyy' NULLIF FIELD_3='',
The Log file shows
Record 1: Rejected - Error on table testtable.Table_name, column FIELD_3.
ORA-01843: not a valid month
The whole point is that its empty, and for sure its not a valid month, but its not recognizing it, when I push a NULL value as a constant on SQL*Loader it works.
I also have another problem where some of my data instead of the date the clients fill in a "0" or a "3*, there is no way of changing the DATA file, as this is a data dump from the clients.
I also read that the BLANKS keyword does nto work on tabs, so I tried everythign, believe me, everythign, I spent about 8 hours on this already with no progress.