Re: SQL Loader - How to load userid without hardcoding

Chor Ling,

If you want to load those rows where Field2 is not null, just put the line WHEN FIELD2 != " "
This will not check the value of Field1, be it null or not null.

If you want to ignore rows where Field1 is not null and Field2 is null (load only those where Field1 is null and Field 2 is not null) then try : WHEN FIELD1=" " and FIELD2 != " "

Keng Cheng
Quekkc_at_sp.ac.sg

Hi Dave, Tim & Keng Cheng !

Thanks 4 your replies.

Your Solutions :
A : Put the line below
WHEN FIELD1 > " "
in control file.

B : Put a not null constraint on the column(s). The offending rows will be rejected and placed into a .bad file.

U've partially solved my problem.

I have > 2 cols in my table
In my control file, I place the line
WHEN FIELD1 != " " OR FIELD2 != " "
Error Occurs.
But if I use
WHEN FIELD1 != " "
It's OK. However I want to load those rows where Field1 is null and field2 is not null.

Can anyone tell me the syntax for OR in SQL Loader ?

TIA ChorLing
chancl_at_nievax.nie.ac.sg

PS. More details

My table Table2 contains field :

1) userid varchar2(30)
2) Tablecounter number(4)
3) Field1 varchar2(12)
4) Field2 varchar2(3)

My data file contains Field1 data from 1 to 12 position and Field2 data from 14 to 16 eg
123456789012 AAA
222222222222 BBB My ctrl file,
LOAD DATA INTO TABLE TABLE1
WHEN FIELD1 != ' ' OR FIELD2 != ' '
( USERID "USER",
 TABLECOUNTER SEQUENCE(MAX,1),
 FIELD1 POSITION(01:12) CHAR,
 FIELD2 POSITION(14:16) CHAR) Received on Fri Mar 01 1996 - 20:14:15 CST