Unable to read the values into other dropdowns using the multiselect compoenent

App Building
,
1

Im using a mutliselect component(multiselect1) followed by another two select components. The first select component(select9) depends on the multiselect component(multiselect1) and the second select component(select10) depends on the (multiselect1) and (select9). Im able to read the values into select9 component but not able to read the values into select10 using the multiselect and select9 values. Its throwing a error like below
image

and my sample setup is like

image
image758×267 9.73 KB

also, it it throwing error like
SQL compilation error: error line 1 at position 115 Bind variable :1 not set.

Kindly help here

2

Try disabling prepared statements on the resource.

Alternatively, build your SQL query in a javascript resource and return the final statement to your query to be executed, like:

DECLARE @STATEMENT nvarchar(max)
SET @STATEMENT = {{createQueryJS.data}} //this would be "select distinct country..."
EXEC (@STATEMENT)
3

Disabling the prepared statements isnt working. Also, im new to js. Can you please give me the exact js query because the above one isn't working

4 
return "select distinct country from ABC where type IN('" + multiselect1.value.join("' , '") + "') AND title IN('" + select9.value + "')"

This would be the JSQuery which gets set as the Statement in my previous post

5

Hi @pyrrho
I have used the jsQuery you have provided and in the snowflake query i have used like-

DECLARE @STATEMENT nvarchar(max)
SET @STATEMENT = {{jsQuery.data}}
EXEC (@STATEMENT)

Its throwing the below error-
image

Sorry, Im just new to this retool development. Can you please help

6

Since you are using Snowflake, I recommend fitting the pattern to match how Snowflake handles variable declaration and execution:

https://docs.snowflake.com/en/sql-reference/sql/execute-immediate

7

I have set it in the query in snowflake. At the end, i have come to the same error Bind variable :1 not set

I dont understand it worked for the first select and is not working for the next one. The query is working when i tried reading the data alone with the multiselect and alone with the select9. But is not working with the other one

8

The regular select--as a single value--is parsed properly as a single entity in the IN clause.

The multiselect--as an array of values--is not parsed properly in the IN clause.

For the multiselect, you can try the type = ANY({{multiselect1.value}}) pattern instead of the IN clause.

9

Also, for Snowflake the declaration and execution statements should look like this, I believe:

DECLARE statement VARCHAR DEFAULT {{jsQuery.data}}
EXECUTE IMMEDIATE statement
10

thanks for the help!!