CASE Expressions
Case expressions provide a decoding capability that allows one expression to be transformed into another. Case expressions can appear anywhere that other forms of expressions can be used.
There are two forms of case expressions:
• Simple
• Searched
Syntax for a simple case expression is as follows:
CASE expr WHEN expr1 THEN expr2 WHEN expr3 THEN expr4... [ELSE exprn] END
The initial case expression is compared in turn to the expressions in each WHEN clause. The result of the case is the expression from the THEN clause corresponding to the first WHEN clause whose expression is equal to the case expression. If none of the WHEN expressions match the case expression, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.
Syntax for the searched case expression is as follows:
CASE WHEN search_conditon1 THEN expr1 WHEN search_expression2 THEN expr2...[ELSE exprn] END
The search conditions of each WHEN clause are evaluated in turn. The result of the case is the expression from the THEN clause corresponding to the first WHEN clause whose search condition evaluates to true. If none of the WHEN clause search conditions evaluate as true, the result is the value of the expression from the ELSE clause. If there is no ELSE clause, the result is the null value.