[jweingarth]

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.

[gbrabham]

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