COALESCE: Returning the First Non-Missing Value

How to:

Return the First Non-Missing Value

Given a list of arguments, COALESCE returns the value of the first argument that is not missing. If all argument values are missing, it returns a missing value if MISSING is ON. Otherwise it returns a default value (zero or blank).

Syntax: How to Return the First Non-Missing Value

COALESCE(arg1, arg2, ...)

where:

arg1, arg2, ...

Any field, expression, or constant. The arguments should all be either numeric or alphanumeric.

Are the input parameters that are tested for missing values.

The output data type is the same as the input data types.

Example: Returning the First Non-Missing Value

COALESCE returns the first non-missing value:

COALESCE(DAMAGED, RETURNS)

The following table shows sample inputs and results.

DAMAGED	RETURNS	RESULT
MISSING	4	4
6	4	6

	WebFOCUS