Home » Developer & Programmer » Data Integration » check for null in SQL*Loader DEFAULTIF clause
check for null in SQL*Loader DEFAULTIF clause [message #93447] Tue, 25 January 2005 09:43 Go to next message
ashish
Messages: 107
Registered: December 2000
Senior Member
Hi

I understand that if the defaultif clause is not specified in the control file for sql*loader, and if the field value is NULL, sql*loader inserts NULL as the value of that field.

However, I have not been able to determine the syntax to set the value of the field to its default value if the value of the field in the data file is null.

I tried doing this:
LOAD DATA
INFILE 'datafile.dat'
APPEND into table tablename
fields terminated by "t"
trailing nullcols(
col1 defaultif (col1 = NULL)
)

But, this does not work. I was wondering if anyone can let me know what should be the syntax of the CONDITION part of the DEFAULTIF clause, so that I can compare it with NULL and set it to default if the value is NULL.

Thanks
Re: check for null in SQL*Loader DEFAULTIF clause [message #93462 is a reply to message #93447] Fri, 04 February 2005 00:20 Go to previous messageGo to next message
Sreedhar Reddy
Messages: 55
Registered: January 2002
Member
check the syntax below

LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
Re: check for null in SQL*Loader DEFAULTIF clause [message #93463 is a reply to message #93462] Fri, 04 February 2005 08:51 Go to previous message
ashish
Messages: 107
Registered: December 2000
Senior Member
Thanks Sreedhar,

It worked like a champ.

-Ashish
Previous Topic: Mappings in OWB and update problems.
Next Topic: Oracle Repository
Goto Forum:
  


Current Time: Fri Jan 17 07:16:23 CST 2020