Use a list of values from a table in a SQL IN statement

Hi team Retool,

I'm trying to pass a list of values from a retool table component, into a SQL statement, so that information can be selected from a SQL table:

To make sure I'm getting the right values, I'm using a JS transformer, then referencing those values in the SQL statement:

The output looks right, but when I run the query, I get no results:

However, if I copy and paste the values directly from the output preview into the query, the SQL statement works:

any ideas as to what the problem might be?

Thanks team.

Try using = ANY(yourlist)

HI Scott,

No dice. If I try using ANY in the SQL statement
"... where webcode = ANY ({{ }}); ... "

then I get this error:

  • message:"Incorrect syntax near '@param1'."

I recall this being and issue using query json with sql. I have to find where I figured out a way around it. Will get back you asap

Might this be the magic you were looking for? Query JSON with SQL Select doesn't allow for conditional statement with arrays - #2 by Nina

Hi Nina,

Think I tried every way possible based on your suggestion, but nothing works. It's really quite odd. If it helps, the process I've got currently is:

  1. import a list of values from a file to a table component
  2. use a js transformer to format that list so I can use it in a sql IN statement. ie creates a string for the values that looks like this: 'value1', 'value2', 'value3'
  3. use a sql statement to then query an ms sql table for information where those values are in the ms sql table.

sounds simple enough - works great if I just manually copy and paste the transformer result into the sql query (not query sql with json) and run it; but if I try to reference the transformer results in the IN statement "... where webcode IN ({{}}) ... " or variations thereof - it just won't work.

even tried this (sql resource) - which didn't work of course:

select web_code
from dbo.sql_table
where web_code in ({{}});

open to suggestions at this point lol

MS sql syntax is included here SQL Cheatsheet - best practices for different flavors of SQL syntax

Thanks Nina. After making some adjustments, things are now working. Although, I am getting an error, even with successful results:

if I make any changes to the setup I now have, I get no data, but the error disappears. I'd rather not disable prepared statements.

Ah, please ignore my earlier reply as I hadn't understood the resource type you were querying. While you could get a roundabout method working down that path of querying json with sql... I would suggest filtering based on your resource type's go-to where clause style

I probably won't be of anymore help than this (sorry!), but you should find something to work that might look similar to this. I imagine if you aren't filtering on an id, rather a string, select * would do the trick and maybe string_split would still work the same as the example given too, but not too sure really without access to a sql server db

select web_code
from dbo.sql_table
where web_code IN ( SELECT * FROM string_split({{ ['value1', 'value2', 'value3'] }}, ',') )

Addressing the error, and, I don't mean to back track, (or suppose the native sql server is inconvenient and query json with sql is preferred), I'm not able to reproduce the error you're seeing about enabling prepared statements on query json with sql. Is the sql_data_table populated with a valid value? Are you on cloud running 3.9.0?

query_json_with_sql_where_in_clause.json (75.1 KB)

Hi Nina,

Like I said it's odd. By all accounts, everything should work fine. In answer to the query, yes, on cloud running 3.9.0. The sql_data_table is populated with valid data. So far, the only way I've been able to get things to work, is using Query JSON with SQL, but it produces that error.

I did try just a standard query, direct to another SQL table using an appropriate resource (same database) with the following code, but it doesn't work either; but it will work, if I manually copy and paste the js.transformer data results into the SQL IN statement.

ie this doesn't work:

but this does:

Little bit frustrating. If there's another way to do achieve the results, would happily try it.

Thanks again.

What were the results of trying the MS Sql Server where clause as laid out in the linked cheatsheet?

Well, string_split wont work as the server in question is pre-21016. With the alternative pre-2016 code, the query ran, but no results were given - basically the same results as trying to pass in the {{}}, in my previous post.

I also tried just using a text field and manually typing the example values in, and referencing that - but again, same result, when query the resource directly.

With the {{ attempt}}, if it was included the double quotes at the open & close, I'd understand the result, but it doesnt. So at the moment, it seems the only option is to use Query SQL with JSON, and get results, while accepting the error displayed.

Slightly weird follow up. I changed the layout of the Query SQL with JSON - and now I dont get the error:


I dont know why it works without error this way - but it does, so I'll take it. Thanks Nina.

1 Like

Oh. Sometimes you take what you can get? ¯\_(ツ)_/¯

1 Like