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