Note: Decode and Case are very similar in their appearance but can produce very different results. | Demo Tables & Data |
| DECODE (overload 1) | standard.DECODE(expr NUMBER, pat NUMBER, res NUMBER) RETURNNUMBER; | 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) RETURNOBJECT; | 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 WHEREclause | 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' WHENFALSE THEN 'FALSE' END); END; / | | CASE - DECODE Comparison | The same functionality written using both functions | SELECT parameter, DECODE(SIGN(parameter-1000),-1,'C','P') ASBAND 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 | |