I have a very simple SQL query that it is using a data point from a previous block. When the value is referenced using curly brackets, I get a null result, when I use the same exact value without refernecing, I get my results. Not sure if this is the expected behaviour and if I'm missing anything:
That's because we parameterize the query. In the first case, the query is constructed as
SELECT invoice_number FROM invoices WHERE
invoice in ($1)
where $1 is
"'INV-0726', 'INV-0728', 'INV-0708'" (a string with those values chained together)
Even though the preview is the same, it's not exactly the same as the latter query.
You should be able to use
ANY, with an array of string value (i.e. don't join them) like the suggestion we have in this thread:
Learn about some best practices for working with SQL! This post is back by popular demand -- we all use SQL in our Retool apps fairly often. Example SQL snippets to use for the various SQL resource types can be super useful to have on hand.
Use arrays in queries
Every SQL database has a slightly different way of handling arrays. Because SQL resources in Retool are set to convert queries to prepared statements by default this can have more challenging interactions with multiple values inside a …