COALESCEEMPTY Function

  • Article

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)