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. Note that unlike 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:

<coalesce_empty> ::= COALESCEEMPTY (<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 evaluates to the empty cell value, then it returns the empty cell value.

Note that COALESCEEMPTY is simply a special case of a CASE expression.

COALESCEEMPTY(V1, V2)

is the same as:

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

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)