CASE Expressions

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.