Snowflake IN operator: issues with more than one IN operator with bound variables

[sgodoshian]

Goal:

Write a snowflake query that has a WHERE clause with more than one IN operator that has values bound to the value of a multiselect component.

Details:

I'm able to use one IN operator with bound values and one with hardcoded values, but when I try to set both IN operators to bound values, I get this SQL compilation error:

SQL compilation error: error line 9 at position 24 Bind variable :1 not set.

Screenshots:

One IN operator with bound values and one with a hardcoded values works:

Two IN operators with bound values results in an error:

[WidleStudioLLP]

Hello @sgodoshian ,

You can use ARRAY and ANY in Snowflake for flexible filtering

WHERE 1 = 1
  AND (
    YEAR_PERIOD_END = ANY (ARRAY_CONSTRUCT({{ multiselect1.value }}))
    OR YEAR_PERIOD_START = ANY (ARRAY_CONSTRUCT({{ multiselect1.value }}))
  )
  AND is_deleted = FALSE

[sgodoshian]

Hi @WidleStudioLLP! Thanks for the reply! I'd tried that before, but it doesn't work for me either:

[AbbeyHernandez]

Hi @sgodoshian,

Maybe you could use ARRAY_CONTAINS() here.

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

SQL Cheatsheet