COALESCE()

Purpose

Function to return the first non-null value from a list, or .NULL. if all values in the list evaluate to NULL

Syntax

COALESCE(<expr1>, <expr2> [, <exprN>...])

See Also

EMPTY(), ETOS(), EVL(), IFNULL(), ISNULL(), NVL(), SET NULL, SET NULLDISPLAY

Description

The COALESCE() function evaluates the expression in <expr1>, and if the expression does not evaluate to NULL, the evaluated result is returned. If the expression in <expr1> does evaluate to NULL, the expression in <expr2> is evaluated. If <expr2> does not evaluate to NULL, the evaluated result is returned. If any additional expressions are specified, these are evaluated in turn. If all expressions in the list evaluate to NULL, the COALESCE() function returns .NULL..

Example

set null on
CREATE TABLE nullon (firstname c(20), lastname c(20))
INSERT INTO nullon (lastname) VALUES ("Smith")
SELECT lastname, coalesce(firstname,"Unknown") from nullon