Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site

http://developer.mimer.com


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

Example

 CASE WHEN col1 < 10  THEN 1
 WHEN col1 >= 10 THEN 2
 ELSE 3
 END

CASE Expression Second Form

Example

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

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.

Short Forms for CASE

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

CASE is NULLIF

where

 NULLIF(x1, x2) 
 

is equivalent to

 CASE 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

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.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL Documentation TOC PREV NEXT INDEX