Returns the first non-missing value found in a list of fields.
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 |