Previous Topic Next topic Print topic


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
Previous Topic Next topic Print topic