Difference between DECODE and CASE in Oracle

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

select salary,empno,
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

19 thoughts on “Difference between DECODE and CASE in Oracle

      1. I Think decode processing time faster because decode is a function and case is a expression

  1. In case you can specify complex conditions not only consider equality for one value (for example >= or an other conditions too)

  2. decode function logic always works on equality operator but searched case expression works on multiple condition for evaluation

  3. you can use use the like operator for comparison in ‘case statements’ however can’t be used in decode

  4. 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);

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s