Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: sqlloader problem

    hie all,
    i have a control file and im trying to truncate the date columnof one field but it doesnt work

    this is how my control file looks like

    Code:
    LOAD DATA
    --INFILE '/app/TRAP/TRAP/TRAP_STAGE_VCA_SC_PINS_V_${YYYYMMDD}.dat'
    APPEND INTO TABLE trap_stage_vca_sc_pins_v_bd
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS
    (
    PROD_ID                          ,
    BATCHNR                         ,
    SERIAL_NUMBER                ,
    DATE_GENERATED              DATE "yyyymmddhh24miss",
    DATE_VALID_TO                DATE "yyyymmddhh24miss",
    STATUS                            ,
    DATE_LSTATE                    DATE "yyyymmddhh24miss",
    VALUE                               ,
    CARD_TYPE                        ,
    QUANTITY                          ,
    DATE_MOD                        "(SELECT trunc(sysdate) FROM dual)"
    )
    im trying to truncate the date_lstate to 'yyyymmdd' but the data comes in in this format yyyymmddhh24miss

    i have tried this way "to_date((:date_lstate),'yyyymmdd')",
    but it doesnt work

    any help

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If data, from the input file, comes in this format, it is a STRING. Try to select a substring of (yyyymmddhh24miss, 1, 8) and apply TO_DATE function to it.

  3. #3
    Join Date
    May 2006
    Posts
    132
    How about:

    Code:
    trunc(to_date('20051201030101','yyyymmddhh24miss'))

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You can't issue selects from inside of sqlldr. Try

    LOAD DATA
    --INFILE '/app/TRAP/TRAP/TRAP_STAGE_VCA_SC_PINS_V_${YYYYMMDD}.dat'
    APPEND INTO TABLE trap_stage_vca_sc_pins_v_bd
    FIELDS TERMINATED BY ','
    TRAILING NULLCOLS
    (
    PROD_ID ,
    BATCHNR ,
    SERIAL_NUMBER ,
    DATE_GENERATED DATE "yyyymmddhh24miss",
    DATE_VALID_TO DATE "yyyymmddhh24miss",
    STATUS ,
    DATE_LSTATE DATE "yyyymmddhh24miss",
    VALUE ,
    CARD_TYPE ,
    QUANTITY ,
    DATE_MOD "trunc(sysdate)"
    )
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    hie,
    i think theres something wrong with my substr syntax, any help

    DATE_LSTATE "trunc(to_date(substr(:date_lstate, 'yyyymmddhh24miss,1,8'))",

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Reading post #5, it seems that you are completely lost ...

    As none of previously suggested options didn't work, perhaps it would be a good idea to provide
    - CREATE TABLE script
    - sample input data file
    - current control file
    and then it will be easier to see what's wrong.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    is the file a fixed width file?
    if so, just use position clause to get the characters you want:

    ie:
    PHP Code:
        RDNG_DT         position(6:9date "DDHH24"
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •