Oracle CASE SQL

Oracle SQL allows you to add "Boolean logic" and branching using the decode and CASE clauses. The case statement is a more flexible extension of the Decode statement. In its simplest form the Oracle CASE function is used to return a value when a match is found:

SELECT last_name, commission_pct,
(CASE commission_pct
WHEN 0.1 THEN ‘Low’
WHEN 0.15 THEN ‘Average’
WHEN 0.2 THEN ‘High’
ELSE ‘N/A’
END ) Commission
FROM employees ORDER BY last_name;

A more complex version is the Searched CASE expression where a comparison expression is used to find a match:

SELECT last_name, job_id, salary,
(CASE
WHEN job_id LIKE 'SA_MAN' AND salary < 12000 THEN '10%'
WHEN job_id LIKE 'SA_MAN' AND salary >= 12000 THEN '15%'
WHEN job_id LIKE 'IT_PROG' AND salary < 9000 THEN '8%'
WHEN job_id LIKE 'IT_PROG' AND salary >= 9000 THEN '12%'
ELSE 'NOT APPLICABLE'
END ) Raise
FROM employees;