ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
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 IN NUMBER,
pat IN NUMBER,
res IN 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 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;
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
