| Date format [message #368292] |
Wed, 01 November 2000 16:51  |
Neeraja
Messages: 8
Registered: November 2000
|
Junior Member |
|
|
Good day to you,
I have written a SQL LOADER script which loads the data into oracle tables from a flat file. The problem is with the date field.
The date field is not null column.
The flat file contains both the values and also the nulls for that date fields. so, to handle that I wrote this script
DATE 'mm/dd/yyyy' NULLIF (BEGIN_DATE=BLANKS)
which will place blank values where ever the date is null. It works fine. But, now instead of Blanks I want to insert the SYSTEM DATE when there is a null for the date field. I tried using the NVL in this way
"NVL(rtrim(ltrim(date,'sysdate')));
but this command gives me system date for all the fields int he date column , even when there is a date.
Kindly help me.
Thanks a lot
Neeraja
|
|
|
|
| Re: Date format [message #368297 is a reply to message #368292] |
Fri, 03 November 2000 18:43  |
Thaniks
Messages: 8
Registered: October 2000
|
Junior Member |
|
|
Hi Neeraja,
Please include the following line in yr script and this will work.
column_name "decode(:column_name,null,sysdate,:column_name)"
------------------------------------------------
Sample script:
------------------------------------------------
load data
infile '/export/home/oracle/temprt.dat'
badfile '/export/home/oracle/temprt.bad'
discardfile '/export/home/oracle/temprt.dsc'
discardmax 0
append
into table temprt
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(name ,
dob "decode(:dob,null,sysdate,:dob)" ,
sal
)
-------------------------------------------------
Thaniks.
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
