Results 1 to 2 of 2

Thread: TO_DATE issues

  1. #1
    Join Date
    Feb 2014
    Posts
    1

    Unanswered: TO_DATE issues

    Hi

    im trying to do a select from a table using to_date function however there are 3 different date values and would like to know how to cater for all 3 of them.

    formats
    -01/06/2014
    -20131104
    -00000000 (this value viewed as null which will happen from time to time and is required)

    The code below works for the bottom 2 of the format, but not the first value. Any ideas

    CODE

    SELECT DISTINCT (TO_NUMBER(column3) * sku.u_tray_quantity) qty,
    column6,
    TO_DATE(decode(column6,'00000000',NULL,'MM/DD/YYYY'))expdate,
    wh.rms_wh loc,
    LTRIM(column1, '0') item,
    LTRIM(column4, '0') palletid,
    CASE WHEN column5 = 'H' THEN
    'ONHOLD'
    ELSE
    'AVAILABLE'
    END project,
    CASE WHEN column5 = 'H' THEN
    (fnc_oh_post + 7)
    ELSE
    fnc_oh_post
    END ohpost,
    dci.item u_dc_inventory1,
    dci.creationdate
    FROM jcff_int_inb_staging_data i
    JOIN scpomgr.item ON item.item = LTRIM(column1, '0')
    JOIN jcff_static_wh_conv wh ON wh.triceps_wh = i.column2
    AND wh.displn_id = item.u_discipline
    JOIN scpomgr.sku sku ON LTRIM(column1, '0') = sku.item
    AND wh.rms_wh = sku.loc
    LEFT OUTER JOIN scpomgr.u_dc_inventory dci ON LTRIM(column1, '0') = dci.item
    AND wh.rms_wh = dci.loc
    AND i.column4 = dci.palletid
    WHERE i.interface_id = 'TRI_PBOH'

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    CASE expression might be an answer.

Tags for this Thread

Posting Permissions

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