CASE Expression

---

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

Rules

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.

Example

 CASE WHEN col1 < 10  THEN 1

WHEN col1 >= 10 THEN 2

ELSE 3

END

CASE Expression Second Form

Rules

The following rules apply to CASE expressions:

• 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.

Example

 CASE col1 WHEN 0 THEN NULL
 WHEN -1 THEN -999
 ELSE col1
 END

Short Forms for CASE

There are two short forms for special CASE expressions: NULLIF and COALESCE.

NULLIF

where

 NULLIF(x1, x2)
 

is equivalent to

 CASE WHEN x1=x2 THEN NULL ELSE x1 END
 

I.e. if the operands are equal, the NULLIF expression has the value NULL, otherwise it has the value of the first operand.

COALESCE

where:

 COALESCE(x1,x2)
 

is equivalent to:

 CASE WHEN x1 IS NOT NULL THEN x1 ELSE x2
 END
 

and:

 COALESCE(x1,x2,...,xn)
 

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.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se