COALESCE

Use COALESCE to replace null values with a more meaningful default value.

Sample usage

COALESCE(Field1, Field2, Field3)

Syntax

COALESCE( field_expression [, field_expression,...])

Parameters

• field_expression - a field or expression. All field_expressions must be of the same type.

Example

Suppose you are tracking multiple racers across multiple heats in a competition. Racers might sit out heats for various reasons. You want to create a First_Time field that contains each racer's first results.

Here's the race data.

Racer	Heat1	Heat2	Heat3
Racer X		38.22	37.61
Racer Y	41.33		38.04
Racer Z	39.27	39.04	38.85

Use the following formula in the First_Time field to get the first non-null value from the Heat columns:

COALESCE(Heat1, Heat2, Heat3)

Results:

Racer	Heat1	Heat2	Heat3	First_Time
Racer X	null	38.22	37.61	38.22
Racer Y	41.33	null	38.04	41.33
Racer Z	39.27	39.04	38.85	39.27