Oracle CASE() Function

Like the IF statement, The Oracle (PL/SQL) CASE() function checks conditions and returns a value. It starts with checking the first condition and keep on checking conditions until it finds the condition true (like an IF-THEN-ELSE statement). Once the condition is found to be true, it stops checking the further conditions and returns the value. If no condition is found to be true, then it returns the value in the ELSE clause. If the ELSE clause is not defined, it returns NULL.

Syntax

The syntax for using CASE statement is given below:

CASE  
   WHEN Condition1 THEN Value1
   WHEN Condition2 THEN Value2
   WHEN Condition3 THEN Value3
   ... 
   ELSE Value
END

Return Value

The CASE statement returns any datatype such as a string, numeric, date, etc. Note that all results must be the same datatype in the CASE statement. If no condition is found to be true, then the CASE statement will return the value in the ELSE clause. If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

Example:

Consider a database table called Employee with the following records:

• To fetch Name and Salary data of the employees categorized by salary (using CASE statement) present in the Employee table , the following SQL code can be used:

  SELECT Name, City, Salary, 
  CASE
    WHEN Salary < 2800 THEN 'Salary is less than 2800.'
    WHEN Salary > 2800 THEN 'Salary is greater than 2800.'
    WHEN Salary = 2800 THEN 'Salary is 2800.'
    ELSE 'Salary Data missing.'
  END AS SalaryinCategory 
  FROM Employee;
  

  
  

  This will produce the result as shown below:

  Name	City	Salary	SalaryinCategory
  John	London	3000	Salary is greater than 2800.
  Marry	New York	2750	Salary is less than 2800.
  Jo	Paris	2800	Salary is 2800.
  Kim	Amsterdam	3100	Salary is greater than 2800.
  Ramesh	New Delhi	3000	Salary is greater than 2800.
  Huang	Beijing	2800	Salary is 2800.

• Consider the example below where the CASE statement is used to order by result based on given condition.

  SELECT * FROM Employee 
  ORDER BY
  (CASE
    WHEN Age <= 27 THEN Age
    ELSE Salary
  END);
  

  
  

  This result of the following code will be:

  EmpID	Name	City	Age	Salary
  2	Marry	New York	24	2750
  1	John	London	25	3000
  3	Jo	Paris	27	2800
  6	Huang	Beijing	28	2800
  5	Ramesh	New Delhi	28	3000
  4	Kim	Amsterdam	30	3100