Use CASE expressions to create conditional logic.
Syntax
CASE {searched-when-clause | simple-when-clause}
[ELSE NULL | ELSE result-expression]
END
searched-when-clause:
WHEN search-condition THEN
{result-expression | NULL} ...
simple-when-clause:
expression WHEN expression THEN
{result-expression | NULL} ...
where:
| searched-when-clause | Specifies a search condition that is applied to each row or group of table data presented for evaluation and the result when that condition is true. |
| simple-when-clause | Specifies that the value of the expression prior to the first WHEN keyword is tested for equality with the value of each expression that follows a WHEN keyword. Also, specifies the result for each WHEN keyword when the expressions are equal. |
| result-expression | Specifies the result of a case-expression if no case is true. Also, specifies the result of a searched-when-clause or a simple-when-clause when true. There must be at least one result-expression in the CASE expression. |
| search-condition | Specifies a condition that is true, false, or unknown about a row or group of rows. The search-condition in a searched-when-clause cannot contain a subselect. |
Example
Assume that in the a table named EMPLOYEE, the first character of a department number represents the division in the organization. Use a CASE expression to list the full name of the division to which each employee belongs:
SELECT EMPNO, LASTNAME CASE SUBSTR(WORKDEPT,1,1) WHEN 'A' THEN 'Administration' WHEN 'B' THEN 'Human Resources' WHEN 'C' THEN 'Design' WHEN 'D' THEN 'Operations' END FROM EMPLOYEE