Can't filter SQL result by array - Snowflake

[Steven_Shoemaker]

I have this code:

SELECT 
    "Fiscal Month", 
    "Fiscal Month#", 
    "Platform", 
    "Subchannel"::VARIANT, 
    SUM("Transaction Revenue") AS "Revenue", 
    SUM("Sessions") AS "Sessions"
FROM 
    DATA_LAKE.TABLEAU_PREP."ALC PR"
WHERE 
    "Fiscal Month" > '2023-01-01' 
    AND "Fiscal Month#" = {{month1.value}} 
    AND "Subchannel" IN ({{multiselectListbox1.value.map(item => `'${item}'`).join(",")}})
GROUP BY 
    "Fiscal Month", 
    "Fiscal Month#", 
    "Platform", 
    "Subchannel";

And prior to this I kept getting an error saying Variant isn't an accepted type. I'm using a multiselectListBox to get an array of values and I'm trying to filter my SQL code using that array.

Any suggestions?

[ScottR]

Try = ANY() instead of IN
And I don't think you need to use map as you should be able to use multiselectListBox1.value as it is already an array

[joeBumbaca]

Hey @Steven_Shoemaker! Using arrays in snowflake can be a bit tricky in Retool. We have a post here that goes into some of the different SQL syntax for arrays.

For your query, this syntax should work:

Using the values from this multiSelect:

Let me know if you have any questions!