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
CASE is a statement which will executes faster than the DECODE, because DECODE is function.
Thanks for the comment. But I don’t think your statement is true.
I Think decode processing time faster because decode is a function and case is a expression
In case you can specify complex conditions not only consider equality for one value (for example >= or an other conditions too)
Thats true. Thanks for the comment
In Decode we can not use relatioal operators but in Case allows relational operators
decode function logic always works on equality operator but searched case expression works on multiple condition for evaluation
When ever we compare null with another null decode returns “TRUE” and case returns “FALSE”
Hi,
I tried this,but both are returns false only
Can u plz provide some examples on this
Your question is not clear, elaborate it.
decode is a function
where case is a expression
case is faster wen compare to decode.
you can use use the like operator for comparison in ‘case statements’ however can’t be used in decode
Hi,
can u please explain the performance tuning and optimization.
There is an error:
select decode( salary = 12000,’high’,10000,’good’,’ok’ ) as decode_test from emp where empno in (10,11,12,13,14,15);
The correct statment is:
select decode( salary,12000,’high’,10000,’good’,’ok’ ) as decode_test from emp where empno in (10,11,12,13,14,15);
thanks for correction
-> In case statement we can use operators but come to decode not possible to use operators..