With a CASE expression, it is possible to specify a conditional value. Depending on the result of one or more conditional expressions, the CASE expression can return different values.
A CASE expression can be in one of the following two forms.
CASE Expression First Form
ExampleCASE WHEN col1 < 10 THEN 1 WHEN col1 >= 10 THEN 2 ELSE 3 END
CASE Expression Second Form
ExampleCASE col1 WHEN 0 THEN NULL WHEN -1 THEN -999 ELSE col1 END
The following rules apply to CASE expressions:
- If one or more search-conditions are true, then the result of the CASE expression is the result of the first (left-most) WHEN clause which has a search-condition that is true.
- If none of the search-conditions are true, then the result of the CASE expression is the result of the explicit or implicit ELSE clause.
- If no ELSE clause is specified then ELSE NULL is implicit.
- At least one result in a CASE expression must express a value different from NULL.
See Result Data Types for a description of how the data type of the result of the CASE expression is determined.
Short Forms for CASE
There are two short forms for special CASE expressions: NULLIF and COALESCE.
CASE is NULLIF
is equivalent toCASE WHEN x1=x2 THEN NULL ELSE x1 END
I.e. if both operands are equal, the NULLIF expression has the value NULL, otherwise it has the value of the first operand.
CASE is COALESCE
is equivalent to:CASE WHEN x1 IS NOT NULL THEN x1 ELSE x2 END
is equivalent to:CASE WHEN x1 IS NOT NULL THEN x1 ELSE COALESCE(x2,...,xn) END
I.e. the COALESCE expression returns the value of the first non-NULL operand, found by working from left to right, or NULL if all the operands equal NULL.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40