COALESCE()
From Lianjapedia
Contents
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