NEED HELP WITH NULLIF using sqlloader

Hi folks.

I am trying to load a simple datafile into a table using sqlloader. I want to substitute values '000000' to null using the nullif statement. my testdata is:

990101
981002
000000
991104
990502
990322

There are no leading or trailing spaces for these records. I want to load this data into a table 'testdates' which contains 1 column 'tdate' defined as date.

My control file is

load data
infile 'datefile.dat'
replace
into table testdates
( tdate position (1:6) char

        nullif (1:6) = '000000' "to_date(:tdate,'yymmdd')")

After running the job my log file shows

"Record 2 discarded - all columns null."

"5 rows successfully loaded."
"1 row not loaded because all fields were null."

I have tried other variations of the control file & datafile (eg. nullif tdate=blanks, fields terminated by "?", insert & append etc).

I have also tried load the data as a simple char field.

load data
infile 'datefile.dat'
replace
into table testdates
( tdate position (1:6) char nullif (1:6) = '000000' )

No matter what I attempt, record 2 is rejected as shown in log file,and the other 5 are loaded OK. I just cannot get nullif to work.

thanks in advance for any help.
--
Mark Mathias Received on Mon Nov 29 1999 - 06:53:47 CST