I'm using temporary state as an input for a snowflake query, the state value is an array of numbers pulled by a different query. My query looks like this:
select l.id, p.name
from table_l l
join table_p on p.id = l.p_id
where l.id in ({{state1.value}})
when I hover over the query it shows me the supposedly correct string version of the query, something like:
select l.id, p.name
from table_l l
join table_p on p.id = l.p_id
where l.id in (1,2,3,4)
which I expect to return 4 values, but it is only returning whatever number is first (so in this case, 1). If I replace the {{state1.value}} with the actual numbers, it pulls in everything I would expect, if I try to treat state1.value as an array, it throws an error stating that the value isn't an array. How do I use the state value correctly to pull in all of the values I want instead of just the first one?
However ARRAY_CONTAINS will only check for exact match. What if I want to get the dimensions whose name contains a certain substrings? This is the exact use case for the LIKE ALL clause with some % for instance:
SELECT * from DIM_DIMENSION where DIMENSION_NAME like all ('%COMPAGNY_1%','%PROVIDER_2%');
Hi @philippe-boyd-maxa! I'll go ahead and make sure you're notified here as well when there's an update on that bug. In the meantime, like @mark mentioned in the linked thread, a potential workaround is to select the option to disable converting your queries to prepared statements.
As you may have read elsewhere - by default, all of our SQL queries are converted to prepared statements to prevent SQL injection, meaning that table/database names and SQL functions aren't able to be defined using a string created dynamically. The main reason we currently convert all statements into prepared statements, is so that users can't enter malicious syntax (like DROP TABLE) into the variable fields.
You can disable this setting in the resource setup, but keep in mind the potential of submitting dangerous SQL through any of the variables referenced in a query. Disabling prepared statements can also break other existing queries. If that's something you'd like to explore, we often recommend setting up another copy of a resource with that setting enabled to help limit the surface area that you have to keep in mind SQL injection for.
Just want to follow up here, in the last couple of weeks we pushed an update that lets you use interpolation for arrays with prepared statements on so something like this should work properly now:
SELECT * from DIM_DIMENSION where DIMENSION_NAME like all ({{['%COMPAGNY_1%','%PROVIDER_2%']}});