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