COALESCE

Some parts in the output code are omitted for clarity reasons.

Description

Evaluates the arguments in order and returns the current value of the first expression that initially doesn't evaluate to NULL. For example, SELECT COALESCE(NULL, NULL, 'third_value', 'fourth_value'); returns the third value because the third value is the first value that isn't null. (COALESCE in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

COALESCE ( expression [ ,...n ] )   

Snowflake SQL

Snowflake SQL Documentation

COALESCE( <expr1> , <expr2> [ , ... , <exprN> ] )

Examples

SQL Server

Code:

IN -> SqlServer_01.sql

SELECT TOP 10 StartDate, 
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL 
FROM HumanResources.EmployeeDepartmentHistory

Result:

StartDate |FIRST_NOT_NULL|
----------+--------------+
2009-01-14|    2000-01-01|
2008-01-31|    2000-01-01|
2007-11-11|    2000-01-01|
2007-12-05|    2010-05-30|
2010-05-31|    2000-01-01|
2008-01-06|    2000-01-01|
2008-01-24|    2000-01-01|
2009-02-08|    2000-01-01|
2008-12-29|    2000-01-01|
2009-01-16|    2000-01-01|

Snowflake SQL

Code:

OUT -> SqlServer_01.sql

SELECT TOP 10
StartDate,
COALESCE(EndDate,'2000-01-01') AS FIRST_NOT_NULL
FROM
HumanResources.EmployeeDepartmentHistory;

Result:

StartDate |FIRST_NOT_NULL|
----------+--------------+
2009-01-14|    2000-01-01|
2008-01-31|    2000-01-01|
2007-11-11|    2000-01-01|
2007-12-05|    2010-05-30|
2010-05-31|    2000-01-01|
2008-01-06|    2000-01-01|
2008-01-24|    2000-01-01|
2009-02-08|    2000-01-01|
2008-12-29|    2000-01-01|
2009-01-16|    2000-01-01|

Known Issues

No issues were found.

Related EWIs

No related EWIs.