Using CASE Expressions in Queries

A CASE expression lets you use IF ... THEN ... ELSE logic in SQL statements without invoking procedures.

The query in the following example uses a CASE expression to show proposed salary increases, based on length of service.

Using a CASE Expression in a Query

SELECT LAST_NAME "Name",
HIRE_DATE "Started",
SALARY "Salary",
CASE
  WHEN HIRE_DATE < TO_DATE('01-Jan-90') THEN TRUNC(SALARY*1.15, 0)
  WHEN HIRE_DATE < TO_DATE('01-Jan-95') THEN TRUNC(SALARY*1.10, 0)
  WHEN HIRE_DATE < TO_DATE('01-Jan-00') THEN TRUNC(SALARY*1.05, 0)
  ELSE SALARY
END "Proposed Salary"
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 100
ORDER BY HIRE_DATE;

Result:

Name                      Started       Salary Proposed Salary
------------------------- --------- ---------- ---------------
Faviet                    16-AUG-94       9000            9900
Greenberg                 17-AUG-94      12000           13200
Chen                      28-SEP-97       8200            8610
Sciarra                   30-SEP-97       7700            8085
Urman                     07-MAR-98       7800            8190
Popp                      07-DEC-99       6900            7245
 
6 rows selected.

Using CASE Expressions in Queries

Related Topics