COALESCE()

Other. Returns the first non-NULL expression from a list of expressions.

Syntax

COALESCE( expression [, ...] )

Parameters
expression	All parameters must be the same data type. If all parameters are NULL, the function returns NULL.

Data Types
Return	expression
Blob	Blob
Boolean	Boolean
Float	Float
Integer	Integer
Interval	Interval
Long	Long
String	String
Timestamp	Timestamp
XML	XML

Example

The following example writes the result of the COALESCE function into the PrimAddress column of the Address stream. If the row from the Employee stream contains a non-NULL value in the Home column, that value is written into PrimAddress. If Home is NULL, but Work is non-NULL, the Work value is written into PrimAddress. Otherwise the word 'None' is written into PrimAddress.

INSERT INTO Address (PrimAddress)
SELECT COALESCE(Home, Work, 'None')
FROM Employee;