Use of DECODE to switch WHERE Clause |
There are situations when reports are created where a drop-down list may have choice of getting data with value of negative, zero or positive, which involves switching the WHERE clause in the query from <0, =0 or >0 respectively. This can be achieved by three ways as exlained below. In business typically negative quantity represents backorder. |
|
Shown in Figure 1 is a data in a table (query table as an
example), consisting of all three types of data (negative, zero and positive values). The column qty_type is a computed value using the DECODE function and does not exist in the database table. This could also represent computed values. |
|
Figure 1 |
SELECT
q_tab.item_cd, q_tab.qty, DECODE(LEAST(q_tab.qty,0), q_tab.qty, DECODE(LEAST(q_tab.qty,0), 0,'ZERO','NEGATIVE'), 'POSITIVE') qty_type FROM ( SELECT 'AAA' as item_cd, 0 as qty FROM DUAL UNION SELECT 'BBB' as item_cd, 10 as qty FROM DUAL UNION SELECT 'CCC' as item_cd, -1 as qty FROM DUAL UNION SELECT 'DDD' as item_cd, -5 as qty FROM DUAL UNION SELECT 'EEE' as item_cd, 8 as qty FROM DUAL UNION SELECT 'FFF' as item_cd, -3 as qty FROM DUAL UNION SELECT 'GGG' as item_cd, 17 as qty FROM DUAL UNION SELECT 'HHH' as item_cd, 0 as qty FROM DUAL UNION SELECT 'III' as item_cd, 9 as qty FROM DUAL ) q_tab WHERE DECODE(LEAST(q_tab.qty,0), q_tab.qty, DECODE(LEAST(q_tab.qty,0), 0,'ZERO','NEGATIVE'),'POSITIVE') = UPPER('&i_qty_type'); |
Figure 2 |
Example: Oracle ERP Applications Query |
SELECT wr.inventory_item_id inv_item_id, |
ohq.transaction_uom_code uom, ohq.transaction_quantity trans_qty, wr.required_quantity reqd_qty, |
NVL(ohq.transaction_quantity,0) - wr.required_quantity Needed_qty, |
DECODE(LEAST(NVL(ohq.transaction_quantity,0) - wr.required_quantity,0), NVL(ohq.transaction_quantity,0) - wr.required_quantity, |
DECODE(LEAST(NVL(ohq.transaction_quantity,0) - wr.required_quantity,0), 0,'ZERO','NEGATIVE'),'POSTIVE') quantity_type |
FROM mtl_onhand_quantities_detail ohq, wip_requirement_operations_v wr |
WHERE ohq.inventory_item_id (+) = wr.inventory_item_id |
AND ohq.subinventory_code(+) = 'ANY_SUBINVCODE' |
AND DECODE(LEAST(NVL(ohq.transaction_quantity,0) - wr.required_quantity,0), NVL(ohq.transaction_quantity,0) - wr.required_quantity, |
DECODE(LEAST(NVL(ohq.transaction_quantity,0) - wr.required_quantity,0), 0,'ZERO','NEGATIVE'),'POSITIVE') = '&i_sign'; |
69981