coalesce

swordfish.function.coalesce()

The function fills null values in X1 and returns a scalar or vector of the same dimension as X1.

For each element in X1,

• If not null, return the element;

• If null, check the element at the same position in X2:

  • If not null, fill the null value in X1 with it;

  • If null, conduct the aforementioned calculation on the subsequent args until a non-null element is returned; Otherwise return NULL.

Usage:

• Merge multiple columns of a table into one column;

• An alternative to complex case expression. For example, select coalesce (expr1, expr2, 1) from t is equivalent to select case when vol1 is not null then vol1 when vol2 is not null then vol2 else 1 end from t.

Parameters:

• X1 (Constant) – A scalar or vector.

• X2 (Constant) – A scalar or vector of the same data type as X1. If X1 is a scalar, X2 must be a scalar; If X1 is a vector, X2 can be a non-null scalar or a vector of the same length as X1.

• args (Constant, optional) – Can be one or more arguments taking the same data type/form as X2.

Returns:

For each element in X1,

• If not null, return the element;

• If null, check the element at the same position in X2:

  • If not null, fill the null value in X1 with it;

  • If null, conduct the aforementioned calculation on the subsequent args until a non-null element is returned; otherwise return NULL.

Return type:

Constant