Oracle DECODE & CASE Functions
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
Purpose Decode and Case are very similar in their appearance but can produce very different results.
 
Demo Tables & Data
Build these objects to run the demos below as the user UWCLASS in PDBDEV
 
DECODE
DECODE (overload 1) standard.decode(
expr IN NUMBER,
pat  IN NUMBER,
res  IN NUMBER)
RETURN NUMBER;
DECODE (overload 2) standard.decode(
expr IN NUMBER,
pat  IN NUMBER,
res  IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 3) standard.decode(
expr NUMBER,
pat  NUMBER,
res  DATE)
RETURN DATE;
DECODE (overload 4) standard.decode(
expr IN VARCHAR2 CHARACTER SET ANY_CS,
pat  IN VARCHAR2 CHARACTER SET expr%CHARSET,
res  IN NUMBER)
RETURN NUMBER;
DECODE (overload 5) standard.decode(
expr IN VARCHAR2 CHARACTER SET ANY_CS,
pat  IN VARCHAR2 CHARACTER SET expr%CHARSET,
res  IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 6) standard.decode(
expr IN VARCHAR2 CHARACTER SET ANY_CS,
pat  IN VARCHAR2 CHARACTER SET expr%CHARSET,
res  IN DATE)
RETURN DATE;
DECODE (overload 7) standard.decode(
expr IN DATE,
pat  IN DATE,
res  IN NUMBER)
RETURN NUMBER;
DECODE (overload 8) standard.decode(
expr IN DATE,
pat  IN DATE,
res  IN VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 9) standard.decode(
expr IN DATE,
pat  IN DATE,
res  IN DATE)
RETURN DATE;
DECODE (overload 10) standard.decode(
expr IN "<OPAQUE_1>",
pat  IN "<OPAQUE_1>",
res  IN "<OPAQUE_1>")
RETURN "<OPAQUE_1>";
DECODE (overload 11) standard.decode(
expr IN "<ADT_1>",
pat  IN "<ADT_1>",
res  IN "<ADT_1>")
RETURN "<ADT_1>";
 
DECODE Demos
Simple DECODE SELECT DECODE(value, <if this value>, <return this value>)
FROM dual;
SELECT program_id, 
  DECODE
(customer_id, 'AAL', 'American Airlines') AIRLINE,
  delivered_date
FROM airplanes
WHERE ROWNUM < 11;
More Complex DECODE SELECT DECODE(value, <if this value>,<return this value>,
                     <if this value>,<return this value>,
                     ....)
FROM dual;
SELECT program_id,
       DECODE(customer_id,
              'AAL', 'American Airlines'
,
              'ILC', 'Intl. Leasing Corp.',
              'NWO', 'Northwest Orient',
              'SAL', 'Southwest Airlines',
              'SWA', 'Sweptwing Airlines',
              'USAF', 'U.S. Air Force') AIRLINE,
       delivered_date
FROM airplanes
WHERE ROWNUM < 11;
DECODE with DEFAULT SELECT DECODE(value,<if this value>,<return this value>,
                     <if this value>,<return this value>,
                     ....
                     <otherwise this default value>)
FROM dual;
SELECT program_id,
       DECODE(customer_id,
             'AAL', 'American Airlines',
             'ILC', 'Intl. Leasing Corp.',
             'NWO', 'Northwest Orient',
             'SAL', 'Southwest Airlines',
             'SWA', 'Sweptwing Airlines',
             'USAF', 'United States Air Force',
             'Not Known') AIRLINE,
       delivered_date
FROM airplanes
WHERE ROWNUM < 11;
Note: The following crosstabulation is the standard for 10g or earlier. In 11g and above use the PIVOT and UNPIVOT operators
Simple DECODE Crosstab

Note how each decode only looks at a single possible value and turns it into a new column
SELECT program_id,
       DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
       DECODE(customer_id, 'DAL', 'DAL') DELTA,
       DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
       DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
FROM airplanes
WHERE rownum < 20;
DECODE as an in-line view with crosstab summation

The above DECODE, in blue, used as an in-line view
SELECT program_id,
       COUNT (AMERICAN) AAL,
       COUNT (DELTA) DAL,
       COUNT (NORTHWEST) NWO,
       COUNT(INTL_LEASING) ILC
FROM (
   SELECT program_id,
          DECODE(customer_id, 'AAL', 'AAL') AMERICAN,
          DECODE(customer_id, 'DAL', 'DAL') DELTA,
          DECODE(customer_id, 'NWO', 'NWO') NORTHWEST,
          DECODE(customer_id, 'ILC', 'ILC') INTL_LEASING
   FROM airplanes)
GROUP BY program_id;
Query for DECODE demo conn uwclass/uwclass@pdbdev

CREATE TABLE stores (
store_name     VARCHAR2(20),
region_dir     NUMBER(5),
region_mgr     NUMBER(5),
store_mgr1     NUMBER(5),
store_mgr2     NUMBER(5),
asst_storemgr1 NUMBER(5),
asst_storemgr2 NUMBER(5),
asst_storemgr3 NUMBER(5));

INSERT INTO stores
VALUES ('San Francisco',100,200,301,302,401,0,403);

INSERT INTO stores
VALUES ('Oakland',100,200,301,0,404,0,0);

INSERT INTO stores
VALUES ('Palo Alto',100,200,0,305,0,405,406);

INSERT INTO stores
VALUES ('Santa Clara',100,250,0,306,0,0,407);
COMMIT;

SELECT DECODE(asst_storemgr1, 0,
      
DECODE(asst_storemgr2, 0,
      
DECODE(asst_storemgr3, 0, 0, asst_storemgr3),
       asst_storemgr2), asst_storemgr1)
ASST_MANAGER,
       DECODE(store_mgr1,0, DECODE(store_mgr2,0, 0, store_mgr2)
,
       store_mgr1)
STORE_MANAGER,
       REGION_MGR,
       REGION_DIR
FROM stores;
DECODE with Summary Function SELECT SUM(CA_COUNT) CA, SUM(TX_COUNT) TX
FROM (
   SELECT state,
   DECODE(state, 'CA', COUNT(*), 0) CA_COUNT,
   DECODE(state, 'TX', COUNT(*), 0) TX_COUNT
   FROM locations
   GROUP BY state);
DECODE in the WHERE clause set serveroutput on

DECLARE
 posn  PLS_INTEGER := 0;
 empid PLS_INTEGER := 178;
 x     NUMBER;
BEGIN
  SELECT NVL(SUM(ah.quantity * ah.saleprice * ap.payoutpct), 0)
  INTO x
  FROM accessoryhistory ah, payoutpercentage ap,
  sku s, store st
  WHERE empid = DECODE(posn,
                          0, st.areadir,
                          1, st.areamgr,
                          2, NVL(st.storemgr1, st.storemgr2),
                          3, NVL(st.asstmgr1, NVL(st.asstmgr2,
                       st.asstmgr3)))

  AND ah.statustype IN ('ACT', 'DEA')
  AND ah.store = st.store
  AND s.dbid = ah.dbid
  AND s.sku = ah.sku
  AND ap.productgroup = s.productgroup
  AND ap.position = posn;

  dbms_output.put_line(x);
END;
/
DECODE altered WHERE Clause

Thanks to HJL
CREATE TABLE test (
pubdate  DATE,
compdate DATE,
valuecol NUMBER(5));

INSERT INTO test VALUES (TRUNC(SYSDATE), TRUNC(SYSDATE+300), 1);
INSERT INTO test VALUES (TRUNC(SYSDATE-300), TRUNC(SYSDATE), 9);
COMMIT;

SELECT * FROM test;

CREATE OR REPLACE PROCEDURE testproc (StartDate DATE, EndDate DATE, DateType IN VARCHAR2)
AUTHID DEFINER IS
 i PLS_INTEGER;
BEGIN
  SELECT valuecol
  INTO i
  FROM test
  WHERE DECODE(DateType, 'AA',compdate, 'BB', pubdate, compdate) <= EndDate
  AND DECODE(DateType, 'AA', compdate, 'BB', pubdate, compdate) >= StartDate;

  dbms_output.put_line(TO_CHAR(i));
END testproc;
/

set serveroutput on

exec testproc(TRUNC(SYSDATE), TRUNC(SYSDATE), 'BB');
 
CASE
Simple CASE Demo CASE (<expression>)
  WHEN (<value>) THEN <result>
  ELSE <result>
END;
SELECT CASE (customer_id)
       WHEN
('DAL') THEN 'Delta Airlines'
       WHEN
('SWA') THEN 'Southwest Airlines'
       ELSE
'Not DAL or SWA'
  END
AS RESULTSET
FROM airplanes
WHERE rownum < 101;
Searched CASE Demo

Thank you HJL for catching the missing "END" in the syntax examples.
CASE WHEN (<expression>) THEN <result>
     WHEN (<expression>) THEN <result>
     ELSE <result>
END;
SELECT line_number,
  CASE WHEN (line_number = 1) THEN 'One'
       WHEN
(line_number = 2) THEN 'Two'
       ELSE
'More Than Two'
  END
AS RESULTSET
FROM airplanes;
More complex Searched CASE Demo with BETWEEN CASE WHEN (<expression> BETWEEN <value> AND <value>) THEN
     WHEN (<expression> BETWEEN <value> AND <value>) THEN
     ELSE <result>
END;
SELECT line_number,
  CASE WHEN (line_number BETWEEN 1 AND 10) THEN 'One'
       WHEN (line_number BETWEEN 11 AND 100) THEN 'Big'
  ELSE 'Bigger'
  END
FROM airplanes;
More Searched Complex CASE Demo With Booleans CASE WHEN (<expression>) THEN <result>
     WHEN (<expression>) THEN <result>
     ELSE <result>
END;
SELECT line_number,
  CASE WHEN (line_number < 10) THEN 'Ones'
       WHEN (line_number < 100) THEN 'Tens'
       WHEN (line_number < 1000) THEN 'Hundreds'
  ELSE 'Thousands'
  END RESULT_SET
FROM airplanes;
The above demo turned into a view CREATE OR REPLACE VIEW line_number_view AS
SELECT line_number,
  CASE WHEN (line_number < 10) THEN 'Ones'
       WHEN (line_number < 100) THEN 'Tens'
       WHEN (line_number < 1000) THEN 'Hundreds'
  ELSE 'Thousands'
  END RESULT_SET
FROM airplanes;
Searched CASE with BOOLEANS set serveroutput on

DECLARE
 boolvar BOOLEAN := TRUE;
BEGIN
  dbms_output.put_line(CASE boolvar WHEN TRUE THEN 'TRUE' WHEN FALSE THEN 'FALSE' END);
END;
/
 
CASE - DECODE Comparison
The same functionality written using both functions SELECT parameter, DECODE(SIGN(parameter-1000),-1,'C','P') AS BAND
FROM parameter_table;

SELECT parameter,
       CASE WHEN parameter < 1000 THEN 'C' ELSE 'P' END AS BAND
FROM parameter_table;
Using SIGN to create CASE-like functionality with DECODE SELECT min_cached, COUNT(*), ROUND(AVG(executions),2)
FROM (
  SELECT DECODE(min_cached,
                         0, '1) 00-01 min',
                         1, '2) 01-02min',
                         2, '2) 01-02min',
         DECODE(SIGN(min_cached -6), -1, '3) 03-05min',
         DECODE(SIGN(min_cached -16), -1, '4) 06-15min',
         DECODE(SIGN(min_cached -31), -1, '5) 16-30min',
         DECODE(SIGN(min_cached -61), -1, '6) 31-60min',
         DECODE(SIGN(min_cached-121), -1, '7) 1-2hrs',
                                          '8) 2 hrs+ ')))))) min_cached,
  executions
  FROM (
    SELECT ROUND((SYSDATE -
    TO_DATE(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*24*60) min_cached,
    executions
    FROM gv$sqlarea
    WHERE parsing_user_id != 0)
    )
GROUP BY min_cached

Related Topics
All Functions
Control Structures
PIVOT
SELECT Statements
String Functions
UNPIVOT

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved