DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Sysdate behaviour in Decode() & NVL()

  1. #1
    Join Date
    May 2002
    Posts
    108

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2002
    Posts
    108

    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

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    May 2002
    Posts
    108

    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
  •  


Click Here to Expand Forum to Full Width