Oracle Case Statement Explained with Tips and Examples

Last updated on December 24th, 2017 at 02:08 pm

We have seen working of Oracle Decode processing in the previous post

Oracle sql decode processing

Now let us see Case statement processing


Case statement in Oracle

It is similar to Decode statement.Databases before Oracle 8.1.6 had only the DECODE function. CASE was introduced in Oracle 8.1.6 as a standard, more meaningful and more powerful function.

Everything DECODE can do, CASE can also . There is a lot else CASE can do though, which DECODE cannot. We’ll go through detailed examples in this article

Lets start with Case statement Syntax

CASE [expression]

when condition_1 then value_1
when condition_2 then value_2
when condition_2 then value_2

else value_n

expression is optional


We can divide the Case statement into two catagories Simple Case statement and Searchable case statement

Simple case statement is just like Decode function.

oracle case statement

Example with Simple CASE statement

when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’


Searchable Case statement are case statement where we specify a condition or predicate (case statement in oracle with multiple conditions)

SQL> select emp_name
, case
when Salary < 10000
then ‘Junior Level’
when (Salary >=10000 and Salary < 50000)
then ‘Middle Level’
when (Salary >= 50000 and Salary < 100000)
then ‘Senior Level’
else ‘Managers’
end Emp_level
from employee_info
where rownum < 5;


JOHN Junior Level
DON Senior Level
BOB Manager
BILL Middle Level

Nested Oracle Case statement

This is case statement with in case statement

SQL> select emp_name
, case
when Salary < 10000
then ‘Junior Level’
when (Salary >=10000 and Salary < 50000)
then ‘Middle Level’
when (Salary >= 50000 and Salary < 100000)
then ‘Senior Level’
else  (Case when grade =’20’  then ‘Vice President’
when grade=’21’  then ‘Senior Vice President’
else ‘Manager’
end Emp_level
from employee_info
where rownum < 5;

Important points about Simple and searchable Case statement

1) The searched CASE evaluates the conditions independently under each of the “when” options. With this structure, far more complex conditions can be implemented with a searched CASE than a simple CASE.

2) A searched CASE can combine multiple tests using several columns, comparisons and AND/OR operators.

3) Both simple and searched CASE constructs, the conditions are evaluated sequentially from top to bottom, and execution exits after the first match is found. So, suppose more than one condition is true, only the first action is considered.

4) Oracle Database uses short-circuit evaluation. That is, for a simple CASE expression, the database evaluates each comparison_expr value only before comparing it to expr, rather than evaluating all comparison_expr values before comparing any of them with expr. Consequently, Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr. For a searched CASE expression, the database evaluates each condition to determine whether it is true, and never evaluates a condition if the previous condition was true


Now lets see the difference between Case and Decode statement

1) DECODE can work with only scalar values but CASE can work with logical operators, predicates and searchable subqueries.

We know that decode can work with scalar values. We cannot use it for Logical operators. We have to convert into scalar values to make use of that.

Case makes the whole process easiar. We can easily use logical operator in the Case statement

SQL> select city
, case
when population < 100000
then ‘Tier I’
when (population >=100000 and population < 200000)
then ‘Tier II’
when (population >= 200000 and population < 300000)
then ‘Tier III’
else ‘TIER IV’
end City_Tier
from city_info
where rownum < 5;




The above is called searchable Case statments


  1. CASE can work as a PL/SQL construct but DECODE is used only in SQL statement.CASE can be used as parameter of a function/procedure.


V_x VARCHAR2(10) := ‘A’;
V_y VARCHAR2(10);


V_y := CASE V_x
WHEN ‘A’ THEN ‘Excellent’
WHEN ‘B’ THEN ‘Good’
WHEN ‘C’ Then ‘Average’
ELSE ‘Poor’

‘Grade V_x is ‘||V_y||’.’


Grade V_x is Excellent.

  1. CASE expects datatype consistency, DECODE does not.

select case 5 when 1 then ‘1’
2 when ‘2’ then ‘2’
3 else ‘5’
4 end
5 from dual;
when ‘2’ then ‘2’
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

  1. CASE complies with ANSI SQL. DECODE is proprietary to Oracle.
  2. CASE executes faster in the optimizer than does DECODE.
  3. CASE is a statement while DECODE is a function.

Related Articles

Oracle documentation on Case

Quick Oracle sql and plsql Tutorials

Top 40 Oracle sql interview questions

How to use oracle PLSQL records

Things you need to know about Oracle SQL developer

Leave a Reply