-
Sysdate behaviour in Decode() & NVL()
Hi
Using SQL*Loader to load data from a CSV file, I faced a unique behaviour of DECODE() & NVL() functions while using Sysdate.
a) Decode(Col, Null,Sysdate,Col) - Returns current date & time
b) Nvl(Col,Sysdate) - Returns only the date part & default time
When one function returns the complete Timestamp, the other doesn't?
Can any of you explain why?
I have attached the sample sql*ldr control file.
LOAD DATA
INFILE 'breakdown.csv'
APPEND
INTO TABLE Breakdown
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(
Sent_To_AR,
Audit_Created_By "NVL (:Audit_Created_By, 'CSD')",
Audit_Date_Created "DECODE (:Audit_Date_Created, NULL,
SYSDATE, :Audit_Date_Created)",
Audit_Changed_By,
Audit_Date_Changed "NVL(:Audit_Date_Changed,SYSDATE)"
)
- Nandu
Never give up !
Nanda Kumar - Vellore
-
May be a silly question, but Audit_Date_Created and Audit_Date_Changed are both date columns aren't they?
Also you seem to be missing the column formats in the control file
-
Audit_Date_Created and Audit_Date_Changed are both date columns aren't they?
They are both Date columns. As the datatype has not been mentioned in the .ctl file, they will be considered the default CHAR.
(As Oracle converts valid character strings to date!)
Surprisingly this conversion is different for these two different funtions? That is what I am puzzled abt.
Any thoughts welcome !
Nandu
Never give up !
Nanda Kumar - Vellore
-
Here's my guess.
NVL() returns a varchar2 by default, and the sysdate is being implicitly converted to varchar2 then back to date using the NLS_DATE_FORMAT picture, hence the time portion is lost.
DECODE() return data type is derived from the type of the first return variable, in this case SYSDATE, so there is no implicit conversion and the time portion is retained.
I expect that proper use of the column format in the control file would eliminate this problem.
-
Here's my guess
Thanks for ur reply Slimdave. Good guess though.
Will try to add the datatype and see if there is any luck.
Nandu
Never give up !
Nanda Kumar - Vellore
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|