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 tostring
usingto_varchar
function, 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
SELECT COALESCE(125, 'hello', cast(850 as format '-999'));
Snowflake
SELECT
COALESCE(TO_VARCHAR(125), 'hello', TO_VARCHAR(850, 'MI000'));
Timestamp mixed with date parameters
Teradata
SELECT COALESCE(cast(TIMESTAMP '2021-09-14 10:14:59' as format 'HH:MI:SSBDD-MM-YYYY'), current_date);
Snowflake
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());
Known Issues
No known issues.
Related EWIs
SSC-EWI-TD0025: Output format not supported.
Last updated