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