coalesce (Function)

Returns the value of the first input parameter that is not null, and all input parameters have to be on the same type.

Syntax

<INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> coalesce(<INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> arg, <INT|LONG|DOUBLE|FLOAT|STRING|BOOL|OBJECT> ...)

Query Parameters

Name	Description	Default Value	Possible Data Types	Optional	Dynamic
arg	This function accepts one or more parameters. They can belong to any one of the available types. All the specified parameters should be of the same type.		INT LONG DOUBLE FLOAT STRING BOOL OBJECT	No	Yes

Example 1

INSERT INTO barStream
SELECT coalesce('123', NULL, '789') AS value
FROM fooStream;

This query selects records from the fooStream collection and uses the coalesce function to return the first non-null value among the provided values ('123', NULL, '789'). In this case, the first non-null value is '123'. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to '123'.

Example 2

INSERT INTO barStream
SELECT coalesce(NULL, 76, 567) AS value
FROM fooStream;

This query selects records from the fooStream collection and uses the coalesce function to return the first non-null value among the provided values (NULL, 76, 567). In this case, the first non-null value is 76. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to 76.

Example 3

@info(name = 'query1')
INSERT INTO barStream
SELECT coalesce(NULL, NULL, NULL) AS value
FROM fooStream;

This query selects records from the fooStream and uses the coalesce function to return the first non-null value among the provided values (NULL, NULL, NULL). In this case, all values are null, so the coalesce function returns NULL. The result is aliased as value and inserted into the barStream.

Essentially, this query processes records in the fooStream and creates new records in the barStream with a constant value field set to NULL.