| check for null in SQL*Loader DEFAULTIF clause [message #93447] |
Tue, 25 January 2005 09:43  |
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  |
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
)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
