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
sqlandplsql.com - Learn SQL and DATABASES 
21 responses to “Difference between DECODE and CASE in 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
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
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.
I Think decode processing time faster because decode is a function and case is a expression
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.
In Decode we can not use relatioal operators but in Case allows relational operators
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.
Your question is not clear, elaborate it.
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
thanks for correction
-> In case statement we can use operators but come to decode not possible to use operators..
can u please explain the performance tuning and optimization
preparing………..
preparing………..
[…] Ans :- Click here […]
Hello There,
The challenge however, is we don’t yet know how it will be used making to hard to assess their value proposition and consequently value.
how to apply precursor in package with multiple
instead of multiple cursors in oracle? Give one example.
Thank you very much and will look for more postings from you.
Regards,
Henry
[…] Difference between DECODE and CASE 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 […]