COALESCEEMPTY Function

OLE DB Programmer's Reference

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 case 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 the following:

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

And the expression

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

is the same as the following:

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

1998-2001 Microsoft Corporation. All rights reserved.