Passing array from selectedRows/multiselect using IN clause

Queries and Resources
1

I have an array created from {{table8.selectedRows.map(row => row.STORE_ID)}} which is a dynamic array from a table with multiselect capabilities. when I try to pass the array using the IN clause, I am receiving a Bind variable :1 not set error. I am able to write the values directly as either a string or as a number and the inserts and updates occurs but using {{table8.selectedRows.map(row => row.STORE_ID)}} throws the errors. For reference I am using snowflake. There seems to be an issue writing the current_user email to the array created from {{table8.selectedRows.map(row => row.STORE_ID)}}

this is the query that throws the error:

MERGE INTO table_a AS target
USING (
SELECT
c.ID,
CASE
WHEN b.ID IS NOT NULL THEN 1 -- ID exists in table_b
ELSE 0 -- ID doesn't exist in table_b
END AS active
FROM table_c c
LEFT JOIN table_b b ON c.ID = b.ID
WHERE c.ID IN ({{table8.selectedRows.map(row => row.ID)}})
) AS source
ON target.ID = source.ID
WHEN MATCHED THEN
UPDATE SET target.active = CASE
WHEN source.active = 1 THEN 0
ELSE 1
END,
target.timestamp = CURRENT_TIMESTAMP(),
target.created_by = {{current_user.email}}
WHEN NOT MATCHED THEN
INSERT (ID, active,TIMESTAMP,CREATED_BY)
VALUES (source.ID, CASE
WHEN source.active = 1 THEN 0
ELSE 1
END,
CURRENT_TIMESTAMP(),
{{current_user.email}});

Any suggestions?

2

I always recommend the = ANY({{yourArrayData}}) over the IN({{yourArrayData}})

in your case I think you want:

WHERE c.ID = ANY({{table8.selectedRows.map(row => row.ID)}})

3

I had to make a subquery inside the IN clause and make use of ARRAY_CONTAINS to get my desired output:

WHERE c.ID IN (
SELECT ID
FROM tablez z
WHERE
ARRAY_CONTAINS(ID::VARIANT, SPLIT( {{table8.selectedRows.map(row => row.ID).join()}}, ',') )
)