Mimer SQL Reference Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Short Forms for CASE


There are two short forms for special CASE expressions.

The first short form for 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.

The second short form for 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 Reference Manual TOC PREV NEXT INDEX