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: 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
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