Oracle Case Statement Explained with Tips and Examples

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

Now let us see Oracle Case statement processing

Table of Contents

Case statement in Oracle

It is similar to the 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

Let’s start with the 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 categories Simple Case statement and Searchable case statement

Simple case statement is just like Decode function.

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 (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;

EMP_NAME EMP_LEVEL
---------- ---------
JOHN Junior Level
DON Senior Level
BOB Manager
BILL Middle Level

Nested Oracle Case statement

This is case statement within the 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)
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.

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 it into scalar values to make use of that.

Case makes the whole process easier. 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 statements

(2) CASE can work as a PL/SQL construct but DECODE is used only in SQL statements.CASE can be used as a 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.

(3) 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

(4) CASE complies with ANSI SQL. DECODE is proprietary to Oracle.

(5) CASE executes faster in the optimizer than does DECODE.

(6) CASE is a statement while DECODE is a function.

Related Articles
Oracle sql Tutorials : Listing of all the sql tutorial lessons which can be used to master sql and use in RDBMS (Oracle,MySql) data management and manipulation
Oracle interview questions: Check out this page for Top 49 Oracle Interview questions and answers : Basics , Oracle SQL to help you in interviews.
oracle PLSQL records : Check out this article on the working of oracle PLSQL records . Also, find out the various ways to define it and assign value to it
Oracle SQL developer tool: Check out this page for all the information on the Oracle sql developer tool,How to do Oracle sql developer download, how to install
oracle date functions : Check out this post for oracle date functions, oracle date difference in years,oracle date difference in days, oracle date difference in months.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions004.htm

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning