MDAC 2.5 SDK - OLE DB Programmer's Reference
Chapter 24: Multidimensional Expressions

COALESCEEMPTY Function

This function is used to coalesce an empty cell value to a number or a string. It is a type of case expression. Unlike for the other types of case expressions—the simple and the searched case—support for COALESCEEMPTY is mandatory.

The BNF construct of the COALESCEEMPTY function is as follows:

<coalesce_empty> ::= COALESCEEMPTY (<value_expression> ,
                                     <value_expression>
                                    [, <value_expression> ]...)

This function is used to coerce an empty cell value to a different value. For example, COALESCEEMPTY(V1, V2) returns V2 if V1 evaluates to the empty cell value; otherwise, it returns V1. COALESCEEMPTY(V1, V2,…, Vn) returns the first (from the left) argument Vk, 1 <= k <= n, which evaluates to a nonempty value. If all arguments evaluate to the empty cell value, COALESCEEMPTY returns the empty cell value.

COALESCEEMPTY is simply a special case of a CASE expression. That is,

   COALESCEEMPTY(V1, V2)

is the same as

   CASE WHEN NOT ISEMPTY(V1) THEN V1 ELSE V2 END

And the expression

   COALESCEEMPTY(V1, V2, ..., Vn), n > 2

is the same as

   CASE WHEN NOT ISEMPTY(V1) THEN V1 ELSE CASE(V1, V2, ..., Vn-1)