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
From a table named EMPLOYEE, find all employees who earn more than 25 percent of their income from commission, but who are not fully paid on commission.
SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE WHERE (CASE WHEN SALARY=0 THEN 0 ELSE COMM/(SALARY+COMM) END >0.25;
Note that this example uses the CASE expression to avoid "division by zero" errors.