Use Retool table column in SQL WHERE query

I want to use the values in a retool table as the 'right-hand-side' of a SQL WHERE clause to query my database. Seems like a noob question - I'm a noob!

This doesn't work, I think, because the data isn't structured right (not a list of objects, not an array (not sure on the right language)). I imagine this is quite easy. Any assistance would be great!

You may want to try using = ANY (formatDataAsArray({{}}))
Posting more information and screenshots always leads to a faster solution.... :slight_smile:

Thanks @ScottR I'm getting the following error:

The retool table:

Populated using the following query:

Cost.CostTotal/DesignCriteria.GIAIncludingBasement AS [Cost Per m2],
from ((((Projects
INNER JOIN DesignCriteria ON Projects.ProjectID = DesignCriteria.ProjectID)
INNER JOIN ProjectType ON ProjectType.ProjectID = Projects.ProjectID)
INNER JOIN Cost ON Cost.ProjectID = Projects.ProjectID)
INNER JOIN ProjectImages ON Projects.ProjectID = ProjectImages.ProjectID)

WHERE (1 = {{ !cascader1.value['3'] ? 1 : 0 }}
OR Projects.ProjectType = {{cascader1.value['3']}})
AND (1 = {{ !select1.value ? 1 : 0 }}
OR ProjectSubType = {{ select1.value }})
AND (1 = {{ !select2.value ? 1 : 0 }}
OR County = {{ select2.value }})

my mistake -- needed more coffee....
= ANY ({{formatDataAsArray(}})

but since the data may already be an array you may only need to do the following:
= ANY ({{}})

Thanks @ScottR

Still not liking it.

What db are you using?
You can also try replace = ANY with IN

Fraid not -

same problem for both queries

OK so two things
perhaps dbo.ProjectID is needed and you need to check the following box in the Resource being used...
Screenshot 2022-12-20 at 11.18.22 AM

Try the first and then the second option...

Thanks, Scott still no luck I'm afraid. I'm using an mssql database. The 'best outcome' I can get is a blank (but apparently successful) query

Using the above queries without IN or ANY


Hey @thomasatkinson! Hopping in here with Scott to try helping as well :slight_smile:

Have you tried

...WHERE ProjectID IN(array)

Syntax from this doc:

And the prepared statements shouldn't need to be disabled since the value is the only dynamic part of the query! They would need to be disabled if your operator or column name were dynamic