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