NEED HELP WITH NULLIF using sqlloader

[Greg Hayes]

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

[Kathinka Diehl]

Greg Hayes <hay...@hayford.demon.co.uk> schrieb:

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

Your statement works fine. But Oracle don't insert a row with _all_ fields
are NULL.

Try this one:

create table testdates
(tdate date,
field varchar2(1));

and

load data
infile yourfile.dat
replace
into table testdates
(tdate position(1:6) char nullif(1:6)='000000'... (your statement),
field position(1:1))

You wil see:

tdate F
----------- -
01-JAN-99 9
02-OCT-99 9
0
04-NOV-99 9
...

HTH, Kathinka

[Martin Haltmayer]

Nice trick.

"trailing nullcols" should also work (before the opening parenthesis
after "insert into" line).

Martin