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

IN -> Teradata_01.sql

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

Result

Snowflake

Query

OUT -> Teradata_01.sql

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

Result

Timestamp mixed with date parameters

Teradata

Query

IN -> Teradata_02.sql

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

Result

Snowflake

Query

OUT -> Teradata_02.sql

SELECT
 COALESCE(TO_DATE(TIMESTAMP '2021-09-14 10:14:59' !!!RESOLVE EWI!!! /*** SSC-EWI-TD0025 - OUTPUT FORMAT 'HH:MI:SSBDD-MM-YYYY' NOT SUPPORTED. ***/!!!), CURRENT_DATE());

Result

Known Issues

No known issues.

Related EWIs

• SSC-EWI-TD0025: Output format not supported.