CASE Statement in Oracle SQL PLSQL

A CASE Statement in Oracle SQL / PLSQL is having the functionality of IF-THEN-ELSE Statement.

Syntax of the CASE Statement in Oracle SQL / PLSQL is:
CASE [expression]
WHEN condition_1 THEN result_1
WHEN consition_2 THEN result_2
WHEN condition_3 THEN result_3
.
.
WHEN condition_N THEN result_N
ELSE default_result
END;

  • expression is an optional value, if provided; it is used to compare with various conditions (e.g. condition_1, condition_2, . . condition_N)
  • condtion_1 to condition_N must have the same data type, also the conditions are evaluated in the order in which they are listed, hence once a condition evaluates to true the CASE statement returns the result and does not evaluate further conditions.
  • result_1 to result_N must also have the same data type, these are the values that will be returned once the condition evaluates to true.
  • If no condition evaluates to true then the ‘default_result’ from the ELSE clause will be returned by CASE statement
  • If the ELSE clause is omitted and none of the condition evaluates to true then NULL will be returned by CASE Statement.

Let’s see an example to understand how to use CASE in Oracle SQL / PLSQL SELECT Statement:

Suppose we have a table named ‘employee’ as shown below:

Employee_ID Employee_Name Salary Department Commission
101 Emp A 10000 Sales
102 Emp B 20000 IT 20
103 Emp C 28000 IT 20
104 Emp D 30000 Support 5
105 Emp E 32000 Sales 10
106 Emp F 20000 Sales 5
107 Emp G 12000 Sales
108 Emp H 12000 Support

Now, if we write our query using CASE Statement in Oracle SQL / PLSQL as:

SELECT employee_id
       ,employee_name
       ,salary
       ,CASE
         WHEN salary = 30000 THEN 'Salary Between 20000 and 30000'
         ELSE 'Salary More Than 30000'
         END SALARY_STATUS
FROM employee;

We will get the following result:

EMPLOYEE_ID EMPLOYEE_NAME SALARY SALARY_STATUS
101 Emp A 10000 Salary Less than 20000
102 Emp B 20000 Salary Less than 20000
103 Emp C 28000 Salary More Than 30000
104 Emp D 30000 Salary Between 20000 and 30000
105 Emp E 32000 Salary Between 20000 and 30000
106 Emp F 20000 Salary Less than 20000
107 Emp G 12000 Salary Less than 20000
108 Emp H 12000 Salary Less than 20000

Here we can observe, that the CASE statement returns the text literals ‘Salary less than 20000’, ‘Salary between 20000 and 30000’ and ‘Salary More Than 30000’ for each record based on the salary amount as specified in the CASE Statement.


Tagged , , , , , , , , , , , . Bookmark the permalink.
  • Tomas Holmstrom ‘blog

    IӶe learn several good stuff here. Certainly price bookmarking for revisiting. I surprise how so much attempt you put to make one of these great informative website.