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”
*Cause:
*Action:
Error at Line: 27 Column: 23

But below statement with CASE will work properly

select salary,empno,
case
when salary = 12000 then ‘high’
when salary = 10000 then ‘good’
else ‘ok’
end salary_details
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.

Oracle Cursors
Find Nth highest salary
Polymorphism in Oracle
Install Notepad++ in Ubuntu
Oracle 12c New Features
Oracle Interview questions and answers
5 Different ways to delete records Oracle