I have a simple query, I want to use a multiselect to choose entry classes, and then filter the table using an IN operator on Azure SQL.
When I hardcode the values (commented out below), it runs fine, and I get results. But when I try to interpret the value from my transformer, it doesn't error, but it doesn't return any rows...
When I copy the query property from the state, and run it directly on the database, it also returns rows... what am I missing here?
(query text from state window)
Try = ANY (classString.value) instead of IN
Thanks, @ScottR , but the database is Azure SQL (mssql), so it doesn't recognize "ANY" as an operator.
Doesn't like " = ANY" either...
Sorry. There must be another operator maybe using LIKE instead. MAYBE?
Or try using formatDataAsArray() around your value.
Will check back tomorrow when at desk. Just throwing it out there
I have a workaround - making a transformer on the big query to filter out the values, and it works, but mostly curious as to why the IN clause seems to fail on Azure SQL...
Hey @pyee! And Scott! Happy to try helping here as well 
Would using the IN (array) syntax work for you? Is there a reason you're passing in "'Opportunity', 'Discussion'" instead of ['Opportunity', 'Discussion']?
My screenshot shows the IN(array) syntax working!
Thanks, @victoria .
That may work in mySQL, but in Microsoft SQL Server, that syntax is invalid (need quoted strings), which is why I had the transformer to build my "in list" string - to get the .query property into valid MSSQL.
I wonder if the fact that my in list is an array of strings rather than INTs makes a difference... can you run that same way if you had review_status or review_type as the filter column? This may be a bug that only exists on SQL Server connections...
The fact that Retool says it ran successfully, but doesn't return any results makes me think it's not even passing that query in exactly the way the state displays it... otherwise it should have kicked this error.
Oh my apologies! Somehow I thought you were using MySQL 
Have you tried using the syntax from these docs? https://docs.retool.com/docs/sql-cheatsheet
We have a winner! That top example for MS SQL Server (2016+) works!
I guess making SQL Server deal with the array with STRING_SPLIT works rather than having Retool try and prepare the IN statement using the parameter syntax.
Thanks for the help!
So happy to hear that! Glad it's working for you + let me know if you get stuck anywhere again 
Thanks!!
