COALESCE

A general SQL function that returns the value of the first expression that is not NULL.

Synopsis

COALESCE(expression,expression)

Arguments

expression

An expression to be evaluated. Multiple expressions are specified as a comma-separated list. This expression list has a limit of 140 expressions. All expressions must be of the same type.

Description

The COALESCE function returns the value of the first non-NULL expression in the expression list. If all expressions evaluate to NULL, NULL is returned.

The type of the return value for the function is the same as the type of the expressions. Currently, Caché always returns a type of %String/VARCHAR.

Examples

The following example uses COALESCE to determine which records contain null values for the FavoriteColors field. It returns a single column. When FavoriteColors is non-null, the color preference is returned; when FavoriteColors is null, the name of the person with no color preference is returned:

SELECT COALESCE(FavoriteColors,Name) AS WhoNullColors 
FROM Sample.Person