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.

  1. Use of dynamic SQL with EXECUTE IMMEDIATE string_sql. Based on user's choice from a drop-down list, the WHERE clause in the string_sql will have to be built with one of the three strings 'qty < 0' or 'qty = 0' or 'qty > 0' using IF-THEN-ELSE syntax (as shown below).
    E.g.: string_sql := 'SELECT col_1,col2 FROM some_table WHERE 1=1 ';
    IF sign_type = 'NEGATIVE' THEN string_sql := string_sql || 'AND qty<0';
    ELSIF sign_type = 'ZERO' THEN string_sql := string_sql || 'AND qty=0';
    ELSIF sign_type = 'POSITIVE' THEN string_sql := string_sql || 'AND qty>0';
    ELSE NULL
    END IF;
    EXECUTE IMMEDIATE string_sql;
  2. Creating a new table/view that will have a sign_type column with values of NEGATIVE for values less than 0, ZERO for value equal to 0 and POSITIVE for value greater than 0 respectively.
  3. Use of DECODE function to dynamically create te sign_type value and use stardard WHERE clause. The query shown below uses the DECODE function method.
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';

Back

Oracle registered trademark of Oracle Corporation.

  23704