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

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