Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    13

    Unanswered: Oracle and Decode Statements

    I have the query below, and cannot make it return the the desired results, in fact it returns nothing. I have determined the problem is with the decode statement, but am unsure as to what I need to change in order for it to work.
    here are the results by running the query without the decode statements C0495

    the t_zone_loca table results are
    WH_ID ZONE LOCATION_ID PICK_SEQ

    GDC CGM1L1 CG0495 0001.

    Pack code for this would be 'CG'

    and t_fwd_pick.location_id is cg0495



    SELECT T_FWD_PICK.location_id
    INTO v_vchpickloc
    FROM T_FWD_PICK, T_LOCATION
    WHERE T_FWD_PICK.item_number = rec_cur_order.item_number
    AND T_FWD_PICK.wh_id = rec_cur_order.wh_id
    AND T_FWD_PICK.TYPE = 'PRM'
    --Handle Shelving
    AND EXISTS (
    SELECT 1
    FROM T_ZONE_LOCA
    WHERE wh_id = rec_cur_order.wh_id
    AND CASE
    WHEN location_id =
    DECODE
    (rec_cur_order.pack_code,
    'CG', T_FWD_PICK.location_id,
    location_id
    )
    AND ZONE =
    DECODE
    (rec_cur_order.pack_code,
    'CG', 'SHV',
    ZONE
    )
    AND ZONE <>
    DECODE
    (rec_cur_order.pack_code,
    'CG', '{{{',
    'SHV'
    )
    THEN 1
    ELSE 0
    END = 1)
    AND T_FWD_PICK.location_id = T_LOCATION.location_id
    AND T_FWD_PICK.wh_id = T_LOCATION.wh_id
    AND T_LOCATION.status != 'I'
    AND ROWNUM = 1;

    thanks in advance for any advice or suggestions that you give.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    decode(variable_to_look_at, 'a', 'b',
    'c', 'd', 'f')

    check variable_to_look_at ... if the value is 'a' use 'b'
    if the value is 'c' use 'd'
    any other value of variable_to_look_at use 'f'

    HTH
    Gregg

Posting Permissions

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