-- CASE is an ANSI SQL function to analyze "IF THEN ELSE".
-- Key advantages of CASE are:
-- 1. ANSI SQL compliant
-- 2. Multiple conditions can be used using AND or OR as necessary
--    in a single condition
-- 3. Easy to analyze and maintain code.

-- The query below has multiple conditions and performing analysis 
-- with DECODE can get complex with cascading DECODE.

WITH q_tab AS (
SELECT 'A' prod_cd, 1   ref_val  FROM DUAL  UNION
SELECT 'B' prod_cd, 2   ref_val  FROM DUAL  UNION
SELECT 'C' prod_cd, -1  ref_val  FROM DUAL  UNION
SELECT 'A' prod_cd, -1  ref_val  FROM DUAL  UNION
SELECT 'B' prod_cd, 0   ref_val  FROM DUAL  UNION
SELECT 'A' prod_cd, 2   ref_val  FROM DUAL  UNION
SELECT 'C' prod_cd, 0   ref_val  FROM DUAL)
SELECT
 prod_cd,  ref_val,
 CASE
   WHEN prod_cd = 'A' AND ref_val >= 0 THEN 'Complete'
   WHEN prod_cd = 'A' AND ref_val < 0  THEN 'WIP'
   WHEN prod_cd = 'B' AND ref_val > 0  THEN 'Complete'
   WHEN prod_cd = 'B' AND ref_val < 0  THEN 'Rework'
   WHEN prod_cd = 'C' AND ref_val > 0  THEN 'Complete'
   WHEN prod_cd = 'C' AND ref_val < 0  THEN 'Rework'
   WHEN prod_cd = 'C' AND ref_val = 0  THEN 'Reject'
   ELSE 'N/A'
 END  prod_status
FROM q_tab;

=> Above query output using Oracle CASE

-- Same query using cascading DECODE with DECODE(SIGN())
-- As shown below, each condition needs a DECODE to evaluate 
-- the prod_status

WITH q_tab AS (
SELECT 'A' prod_cd, 1   ref_val  FROM DUAL UNION
SELECT 'B' prod_cd, 2   ref_val  FROM DUAL UNION
SELECT 'C' prod_cd, -1  ref_val  FROM DUAL UNION
SELECT 'A' prod_cd, -1  ref_val  FROM DUAL UNION
SELECT 'B' prod_cd, 0   ref_val  FROM DUAL  UNION
SELECT 'A' prod_cd, 2   ref_val  FROM DUAL  UNION
SELECT 'C' prod_cd, 0   ref_val  FROM DUAL)
SELECT
 prod_cd,  ref_val,
 DECODE(prod_cd,'A',
  DECODE(SIGN(ref_val),-1,'WIP','Complete'),
   DECODE(prod_cd,'B',
    DECODE(SIGN(ref_val),-1,'Rework',0,'N/A','Complete'),
     DECODE(prod_cd,'C',
      DECODE(SIGN(ref_val),
        -1,'Rework',0,'Reject','Complete'),
        'N/A'))) prod_status
FROM q_tab;

=> Above query output using Oracle DECODE/SIGN


-- According to Oracle, the DECODE() function can be used in a 
-- cascading series - DECODE(value,DECODE(value,DECODE(VALUE,...))) 
-- or to analyze single condition DECODE(value,1,2,3,..) to a 
-- maximum value of 255.

-- Below is an example of Oracle function DECODE along with GREATEST 
-- to compute the quarter number in a number range of 1 to 12.

SELECT
 q_tab.v_val month_no,
 DECODE(GREATEST(q_tab.v_val,4), q_tab.v_val,
  DECODE(GREATEST(q_tab.v_val,7), q_tab.v_val,
    DECODE(GREATEST(q_tab.v_val,10), q_tab.v_val,4,3),2),1)  Quarter 
FROM
  (SELECT LEVEL v_val FROM DUAL CONNECT BY LEVEL <=12) q_tab;

Refer => Quarter Analysis Query