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 |