coalesce

coalesce(X1, X2, args...)

X1 is a scalar or vector.

X2 is 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 (optional) can be one or more arguments taking the same data type/form as X2.

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.

coalesce(int(NULL), int(NULL), 1, 3)
//output
1

coalesce(-1 NULL 4 3, NULL 2 NULL 1, 1 4 5 2)
//output
[-1,2,4,3]

vol1 = [3.3, 2.2, 2.1, NULL, 1.2]
vol2 = [NULL, 1.8, 1.9, 2.3, 3.2]
sym = `a`a`b`a`c
t = table(sym, vol1, vol2)

select sym, coalesce(vol1, vol2) as vol from t