Oracle CASE Expressions

Your rating: None Average: 3.5 (2 votes)

I have used Oracle CASE in one of my previous articles and this is an attempt to explain how it works.

CASE expressions let you use IF ... THEN ... ELSE logic in SQL statements without having to invoke procedures.

Here is a simple CASE example from Oracle documentation:

Simple CASE Example
For each customer in the sample customers table, the following statement lists the credit limit as "Low" if it equals $100, "High" if it equals $5000, and "Medium" if it equals anything else.

SELECT cust_last_name,
   CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium' END
   FROM customers;

CUST_LAST_NAME CASECR
-------------------- ------
Bogart Medium
Nolte Medium
Loren Medium
Gueney Medium

Searched CASE Example


The following statement finds the average salary of the employees in the sample table e.employees, using $2000 as the lowest salary possible:


SELECT AVG(CASE WHEN e.salary > 2000 THEN e.salary
ELSE 2000 END) "Average Salary" from employees e;

Average Salary
--------------
6461.68224

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!