COALESCE

Translation specification for the transformation of the Teradata COALESCE function to its Snowflake counterpart

Description

The coalesce function is used to return the first non-null element in a list. For more information check COALESCE.

COALESCE(element_1, element_2 [, element_3, ..., element_n])

Both Teradata and Snowflake COALESCE functions allow mixing numeric with string and date with timestamp parameters. However, they handle these two cases differently:

• Numeric along with string parameters: Teradata converts all numeric parameters to varchar while Snowflake does the opposite

• Timestamp along with date parameters: Teradata converts all timestamps to date while Snowflake does the opposite

To ensure functional equivalence in the first case, all numeric parameters are cast tostringusingto_varcharfunction, this takes the format of the numbers into account. In the second case, all timestamps are casted to date using to_date, Teradata ignores the format of timestamps when casting them so it is removed during transformation.

Sample Source Patterns

Numeric mixed with string parameters

Teradata

Query

 SELECT COALESCE(125, 'hello', cast(850 as format '-999'));

Result

COLUMN1|
-------+
125    |

Snowflake

Query

SELECT
COALESCE(TO_VARCHAR(125), 'hello', TO_VARCHAR(850, 'MI000'));

Result

COLUMN1|
-------+
125    |

Timestamp mixed with date parameters

Teradata

Query

 SELECT COALESCE(cast(TIMESTAMP '2021-09-14 10:14:59' as format 'HH:MI:SSBDD-MM-YYYY'), current_date);

Result

COLUMN1    |
-----------+
2021-09-14 |

Snowflake

Query

SELECT
COALESCE(TO_DATE(TIMESTAMP '2021-09-14 10:14:59' /*** MSC-WARNING - MSCEWI2025 - OUTPUT FORMAT 'HH:MI:SSBDD-MM-YYYY' NOT SUPPORTED. ***/), current_date);

Result

COLUMN1    |
-----------+
2021-09-14 |

Known Issues

No known issues.

Related EWIs

No related EWIs.