DECODE function and CASE statement are very similar but CASE is an extended version of DECODE. CASE is introduced in Oracle 9i version. I would say there can be two differences. Wait a minute. Is it a difference ? not sure. still………..
1. Decision making statements cannot be used in the place of DECODE expression
DECODE(expression,search1, result1, search2, result2,…., searchN, resultN, default)
Below statement will return an error
select decode( salary = 12000,’high’,10000,’good’,’ok’ ) as decode_test from emp where empno in (10,11,12,13,14,15);
ORA-00907: missing right parenthesis
00907. 00000 – “missing right parenthesis”
Error at Line: 27 Column: 23
But below statement with CASE will work properly
when salary = 12000 then ‘high’
when salary = 10000 then ‘good’
from emp e where e.empno in (10,11,12,13,14);
2. CASE can be directly used in PL/SQL but DECODE can be used in PL/SQL through SQL only
Note : – All examples tested in Oracle 11g Release 2.
Any other difference ? Please comment.