Search code examples
snowflake-cloud-data-platformsnowflake-schemasnowflake-task

Concatenation issues with in-built functions

Can someone please explain me why it returns only two records instead of three? I want the same result when I hard-code the delimiter as well as use the in-built SQL function(s).

SELECT 'HASH(IFNULL(COST_CENTER, '''') || IFNULL(MATCH_DATE, ''''))::bigint'
UNION 
SELECT CONCAT('HASH(', CONCAT_WS(' || ', 'IFNULL(COST_CENTER, '''')', 'IFNULL(MATCH_DATE, '''')' ), ')::bigint')
UNION 
SELECT CONCAT('HASH(', CONCAT_WS('||', ' IFNULL(COST_CENTER, '''') ', ' IFNULL(MATCH_DATE, '''') ' ), ')::bigint')
;
Solution

  • Your first 2 lines are the same, so when you use a UNION, it dedupes them down to a single record. If you want all three lines, use a UNION ALL instead.