PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle DECODE & CASE Functions
Version 11.1
Note: Decode and Case are very similar in their appearance but can produce very different results.
Demo Tables & Data
Airplanes Table Locations Table All other tables
DECODE (overload 1) standard.DECODE(expr NUMBER, pat NUMBER, res NUMBER) RETURN NUMBER;
DECODE (overload 2) standard.DECODE(
expr NUMBER,
pat  NUMBER,
res  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 VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  NUMBER)
RETURN NUMBER;
DECODE (overload 5) standard.DECODE(
expr VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 6) standard.DECODE(
expr VARCHAR2 CHARACTER SET ANY_CS,
pat  VARCHAR2 CHARACTER SET expr%CHARSET,
res  DATE)
RETURN DATE;
DECODE (overload 7) standard.DECODE(expr DATE, pat DATE, res NUMBER) RETURN NUMBER;
DECODE (overload 8) standard.DECODE(
expr DATE,
pat  DATE,
res  VARCHAR2 CHARACTER SET ANY_CS)
RETURN VARCHAR2 CHARACTER SET res%CHARSET;
DECODE (overload 9) standard.DECODE( expr DATE, pat DATE, res DATE) RETURN DATE;
DECODE (overload 10) standard.DECODE(expr OBJECT, pat  OBJECT, res OBJECT) RETURN OBJECT;
DECODE (overload 11) standard.DECODE(expr UNDEFINED, pat UNDEFINED, res UNDEFINED)
RETURN UNDEFINED;
 
Decode Built-in Function

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 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 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 		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) 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 		SELECT CASE WHEN (<column_value>= <value>) THEN
            WHEN (<column_value> = <value>) THEN
            ELSE <value>
FROM <table_name>;
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 CASE Demo With Between		 SELECT CASE WHEN (<column_value> BETWEEN <value> 
             AND <value>) THEN
            WHEN (<column_value>
            BETWEEN <value> AND <value>) THEN
            ELSE <value>
FROM <table_name>;
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 Complex CASE Demo With Booleans		 SELECT CASE WHEN (<column_value> <= <value>) THEN
            WHEN (<column_value> <= <value>) THEN
            ELSE <value>
FROM <table_name>;
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;

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;

Another example using SIGN		 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
PIVOT
UNPIVOT
Select
String Functions
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----