Multi-select value passthrough to SQL

Queries and Resources
1

  • Goal: Trying to pass multi-select values into a Snowflake SQL query as parameters

  • Steps: Tried several different types of syntaxes - but end up with one of two errors ("SQL compilation error: Unsupported data type 'VARIANT'." or Payloadsize = 2 - which doesn't actually generate any results and only works if I remove other filters)

  • Details:

SELECT 
    TO_CHAR(DATE_TRUNC('week', date_column), 'YYYY-MM-DD') AS date_week,
    COUNT(CASE WHEN condition_column IN ('Y', 'Y+') THEN 1 END) AS aggregate_count
FROM 
    schema_name.table_name
WHERE 
    lead_column IS NOT NULL
    AND DATE_TRUNC('week', date_column) < DATE_TRUNC('week', CURRENT_DATE)
    AND DATE_TRUNC('week', date_column) >= DATE_TRUNC('week', CURRENT_DATE) - 7 * {{ week_variable }}
    AND lead_column IN ({{ filter_variable.value.map(val => `'${val}'`).join(', ') }})
GROUP BY 
    date_week
ORDER BY 
    date_week DESC
2

Hi @Rocky_Gowni, welcome to the forum! :wave:

To use arrays in SQL for a Snowflake resource, we want to use this syntax:

SELECT
  *
FROM
  PUBLIC.USERS
WHERE
  ARRAY_CONTAINS(ID::variant, SPLIT( {{[123,224].join()}}, ',') )

Here is our SQL Cheatsheet.