| Note:Decode and Case are very similar in their appearance but can produce very different results. |
| Demo Tables & Data |
|
| DECODE (overload 1) |
standard.DECODE(exprNUMBER,patNUMBER,resNUMBER)RETURNNUMBER; |
| DECODE (overload 2) |
standard.DECODE(
exprNUMBER,
patNUMBER,
resVARCHAR2CHARACTER SET ANY_CS)
returnVARCHAR2CHARACTER SET res%CHARSET; |
| DECODE (overload 3) |
standard.DECODE(exprNUMBER,resDATE)RETURNDATE; |
| DECODE (overload 4) |
standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
patVARCHAR2CHARACTER SET expr%CHARSET,
resNUMBER)
RETURNNUMBER; |
| DECODE (overload 5) |
standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
resVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET res%CHARSET; |
| DECODE (overload 6) |
standard.DECODE(
exprVARCHAR2CHARACTER SET ANY_CS,
resDATE)
RETURNDATE; |
| DECODE (overload 7) |
standard.DECODE(exprDATE,patDATE,resNUMBER)RETURNNUMBER; |
| DECODE (overload 8) |
standard.DECODE(
exprDATE,
patDATE,
resVARCHAR2CHARACTER SET ANY_CS)
RETURNVARCHAR2CHARACTER SET res%CHARSET; |
| DECODE (overload 9) |
standard.DECODE( exprDATE,patDATE,resDATE)RETURNDATE; |
| DECODE (overload 10) |
standard.DECODE(exprOBJECT,patOBJECT,resOBJECT)RETURNOBJECT; |
| DECODE (overload 11) |
standard.DECODE(exprUNDEFINED,patUNDEFINED,resUNDEFINED)
RETURNUNDEFINED; |
|
| Decode Built-in Function |
Simple DECODE |
SELECTDECODE(value,<if this value>,<return this value>)
FROMDUAL; |
SELECTprogram_id,
DECODE(customer_id,'AAL','American Airlines') AIRLINE,
delivered_date
FROMairplanes
WHEREROWNUM< 11; |
More Complex DECODE |
SELECTDECODE(value,<if this value>,<return this value>,
<if this value>,<return this value>,
....)
FROMDUAL; |
SELECTprogram_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
FROMairplanes
WHEREROWNUM< 11; |
DECODE with DEFAULT |
SELECTDECODE(value,<return this value>,
<if this value>,
....
<otherwise this value>)
FROMDUAL; |
SELECTprogram_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
FROMairplanes
WHEREROWNUM< 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 |
SELECTprogram_id,
DECODE(customer_id,'AAL')AMERICAN,'DAL','DAL')DELTA,'NWO','NWO')NORTHWEST,'ILC','ILC')INTL_LEASING
FROMairplanes
WHEREROWNUM< 20; |
DECODE as an in-line view with crosstab summation |
The above DECODE,in blue,used as an in-line view |
SELECTprogram_id,
COUNT(AMERICAN) AAL,
COUNT(DELTA) DAL,
COUNT(NORTHWEST) NWO,
COUNT(INTL_LEASING) ILC
FROM(
SELECTprogram_id,'ILC')INTL_LEASING
FROMairplanes)
GROUP BYprogram_id; |
Query for DECODE demo |
CREATETABLEstores (
store_name VARCHAR2(20),
region_dir NUMBER(5),
region_mgrNUMBER(5),
store_mgr1NUMBER(5),
store_mgr2NUMBER(5),
asst_storemgr1NUMBER(5),
asst_storemgr2NUMBER(5),
asst_storemgr3NUMBER(5));
INSERTINTOstores
VALUES('San Francisco',100,200,301,302,401,403);
INSERTINTOstores
VALUES('Oakland',404,0);
INSERTINTOstores
VALUES('Palo Alto',305,405,406);
INSERTINTOstores
VALUES('Santa Clara',250,306,407);
COMMIT;
SELECTDECODE(asst_storemgr1,
DECODE(asst_storemgr2,
DECODE(asst_storemgr3,asst_storemgr3),
asst_storemgr2),asst_storemgr1)ASST_MANAGER,
DECODE(store_mgr1,DECODE(store_mgr2,store_mgr2),
store_mgr1)STORE_MANAGER,
REGION_MGR,
REGION_DIR
FROMstores; |
DECODE with Summary Function |
SELECTSUM(CA_COUNT) CA,SUM(TX_COUNT) TX
FROM(
SELECTstate,
DECODE(state,'CA',COUNT(*),0) CA_COUNT,'TX',0) TX_COUNT
FROMlocations
GROUP BYstate); |
DECODE in theWHEREclause |
set serveroutput on
DECLARE
posnPLS_INTEGER:= 0;
empidPLS_INTEGER:= 178;
xNUMBER;
BEGIN
SELECTNVL(SUM(ah.quantity * ah.saleprice * ap.payoutpct),0)
INTOx
FROMaccessoryhistory ah,payoutpercentage ap,
sku s,store st
WHEREempid =DECODE(posn,
0,st.areadir,
1,st.areamgr,
2,NVL(st.storemgr1,st.storemgr2),
3,NVL(st.asstmgr1,NVL(st.asstmgr2,
st.asstmgr3)))
ANDah.statustypeIN('ACT','DEA')
ANDah.store = st.store
ANDs.dbid = ah.dbid
ANDs.sku = ah.sku
ANDap.productgroup = s.productgroup
ANDap.position = posn;
dbms_output.put_line(x);
END;
/ |
DECODE Altered WHERE Clause
Thanks to HJL |
CREATETABLEtest (
pubdateDATE,
compdateDATE,
valuecolNUMBER(5));
INSERTINTOtestVALUES(TRUNC(SYSDATE),TRUNC(SYSDATE+300),1);
INSERTINTOtestVALUES(TRUNC(SYSDATE-300),TRUNC(SYSDATE),9);
COMMIT;
SELECT*FROMtest;
CREATEOR@L_502_185@PROCEDUREtestproc (
StartDateDATE,EndDateDATE,DateTypeINVARCHAR2) IS
iPLS_INTEGER;
BEGIN
SELECTvaluecol
INTOi
FROMtest
WHEREDECODE(DateType,'AA',compdate,'BB',pubdate,compdate) <= EndDate
ANDDECODE(DateType,compdate) >= StartDate;
dbms_output.put_line(TO_CHAR(i));
ENDtestproc;
/
set serveroutput on
exec testproc(TRUNC(SYSDATE),'BB'); |
|
| CASE |
Simple CASE Demo |
SELECTCASEWHEN(<column_value>=<value>)THEN
WHEN(<column_value>=<value>)THEN
ELSE<value>
FROM<table_name>; |
SELECTline_number,
CASEWHEN(line_number = 1)THEN'One'
WHEN(line_number = 2)THEN'Two'
ELSE'More Than Two'
ENDASRESULTSET
FROMairplanes; |
More Complex CASE Demo With Between |
SELECTCASEWHEN(<column_value>BETWEEN<value>
AND<value>)THEN
WHEN(<column_value>
BETWEEN<value>AND<value>)THEN
ELSE<value>
FROM<table_name>; |
SELECTline_number,
CASEWHEN(line_numberBETWEEN1AND10)THEN'One'
WHEN(line_numberBETWEEN11AND100)THEN'Big'
ELSE'Bigger'
END
FROMairplanes; |
More Complex CASE Demo With Booleans |
SELECTCASEWHEN(<column_value><=<value>)THEN
WHEN(<column_value><=<value>)THEN
ELSE<value>
FROM<table_name>; |
SELECTline_number,
CASEWHEN(line_number < 10)THEN'Ones'
WHEN(line_number < 100)THEN'Tens'
WHEN(line_number < 1000)THEN'Hundreds'
ELSE'Thousands'
ENDRESULT_SET
FROMairplanes; |
The above demo turned into a view |
CREATEOR@L_502_185@VIEW line_number_view AS
SELECTline_number,
CASEWHEN(line_number < 10)THEN'Ones'
WHEN(line_number < 100)THEN'Tens'
WHEN(line_number < 1000)THEN'Hundreds'
ELSE'Thousands'
ENDRESULT_SET
FROMairplanes; |
CASE with BOOLEANS |
set serveroutput on
DECLARE
boolvarBOOLEAN:=TRUE;
BEGIN
dbms_output.put_line(CASEboolvarWHENTRUETHEN'TRUE'WHENFALSETHEN
'FALSE'END);
END;
/ |
|
| CASE - DECODE Comparison |
The same functionality written using both functions |
SELECTparameter,DECODE(SIGN(parameter-1000),-1,'C','P')ASBAND
FROMparameter_table;
SELECTparameter,
CASEWHENparameter < 1000THEN'C'ELSE'P'ENDASBAND
FROMparameter_table; |
Another example using SIGN |
SELECTmin_cached,COUNT(*),ROUND(AVG(executions),2)
FROM(
SELECTDECODE(min_cached,'1) 00-01 min',
1,'2) 01-02min',
2,
DECODE(SIGN(min_cached -6),'3) 03-05min',
DECODE(SIGN(min_cached -16),'4) 06-15min',
DECODE(SIGN(min_cached -31),'5) 16-30min',
DECODE(SIGN(min_cached -61),'6) 31-60min',
DECODE(SIGN(min_cached-121),'7) 1-2hrs',
'8) 2 hrs+ ')))))) min_cached,
executions
FROM(
SELECTROUND((SYSDATE-
TO_DATE(first_load_time,'YYYY-MM-DD/HH24:MI:SS'))*24*60) min_cached,
executions
FROMgv$sqlarea
WHEREparsing_user_id != 0)
)
GROUP BYmin_cached |
|
