COALESCE
Returns the first value in a given list that is not null
.
Syntax
COALESCE(VALUE1, VALUE2[, ...])
Arguments
VALUE
VALUE
Type: any
A list of values of any type.
Returns
Type: same as input
The first VALUE
that is not null
.
Examples
Transformation job example
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
COALESCE(3.4, 66.0, 744.7) AS coalesce_example1,
COALESCE('a', null::string, 'z', 'h', 'k') AS coalesce_example2,
COALESCE(null::date, date '2022-08-14', date '2022-02-05') AS coalesce_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
coalesce_example1
coalesce_example2
coalesce_example3
3.4
a
2022-08-14
Example with arrays
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
COALESCE(ARRAY[1.1, 0.2], ARRAY[3.0]) AS coalesce_example1,
COALESCE(ARRAY[null::double, 1.1, 0.2], ARRAY[3.0]) AS coalesce_example2,
COALESCE([null::double], [1.1, 0.2], [3.0]) AS coalesce_example3
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
LIMIT 1;
Query result
coalesce_example1
coalesce_example2
coalesce_example3
[1.1, 0.2]
[1.1, 0.2]
[1.1, 0.2]
In arrays containing null and non-null values, Upsolver ignores null values when working with the array. For example, the first array in coalesce_example2
is not considered a null value.
Conversely, an array containing only null values is considered null
, so coalesce_example3
returns the second array provided.
Last updated