Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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:

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:

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: COALESCE and NULLIF.

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.

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.


Mimer
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX