Oracle Case Statement Explained with Tips and Examples



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

Oracle sql decode processing

Case statement in Oracle 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
end

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

select
case
region
when ‘N’ then ’North’
when ‘S’ then ’South’
when ‘E’ then ’East’,
when ‘W’ then ’West’
else ‘UNKNOWN’
end
from
customer;

 

Searchable Case statement are case statement where we specify a condition or predicate


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;

EMP_NAME EMP_LEVEL


JOHN Junior Level
DON Senior Level
BOB Manager
BILL Middle Level

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;

CITY CITY_TIER


XYX TIER I
XYZ TIER II
XZW TIER II

 

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.

Example
DECLARE

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

BEGIN

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

DBMS_OUTPUT.PUT_LINE(
‘Grade V_x is ‘||V_y||’.’
);

END;
/

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

Oracle sql and plsql

Top 40 Oracle sql interview questions

Everything about Oracle PLSQL records


Leave a Reply