- In a simple CASE expression, the SQL searches for the first WHEN……THEN pair for which expr is equal to comparison_expr and returns return_expr. If above condition is not satisfied, an ELSE clause exists, the SQL returns else_expr. Otherwise, returns NULL.
- You can use the CASE expression in a clause or statement that allows a valid expression. For example, you can use the CASE expression in statements such as SELECT, DELETE, and UPDATE or in clauses such as SELECT, ORDER BY, and HAVING.
WHEN when_expression_1 THEN result_1
WHEN when_expression_2 THEN result_2
WHEN when_expression_3 THENresult_3
ELSE else_result
END
======================================================================
Simple CASE Expression
SELECT
first_name, last_name, emp_joiningdate,
CASE (2028 - Extract(year from emp_joiningdate))
WHEN 16 THEN '16 year'
WHEN 10 THEN '10 years'
END aniversary
FROM emp1 ORDER BY first_name;
Searched CASE Expression
SELECT first_name, last_name, salary,
CASE
WHEN salary < 3000 THEN 'Low'
WHEN salary >= 3000 AND salary <= 5000 THEN 'Average'
WHEN salary > 5000 THEN 'High'
END evaluation
FROM emp1 WHERE ROWNUM <= 6;
