112. CASE Expressions - llighter/database GitHub Wiki

CASE Expressions Oracle Help Center

Syntax

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

simple_case_expression::=

searched_case_expression::=

else_clause::=

Oracle Database uses short-circuit evaluation. Oracle never evaluates a comparison_expr if a previous comparison_expr is equal to expr.

Simple CASE Example

For each customer in the sample oe.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 oe.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