Select Query Table based on dropdown value

App Building
, ,
1

I want to select table inside my query to be dynamically changed depends on the dropdown value. So for example:

Dropdown value is A, then I want to SELECT * FROM A
if dropdown value is B, then I want to SELECT * from B

How to achieve this? I tried to change the table with dropdown value but wont work.

Real example:
select id, id_proveedor, nombre, apellidos, email, genero, ip, fecha_colecta, date_update, campanya from {{ddown_table.value}}

But this won't work.

message:"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''usuarios_uk'
at line 1"

2

Hey @yeftanma!

By default Retool uses prepared statements to pass in dynamic values to SQL queries and prepared statements don't support variable database names.

You can try writing two separate SQL queries and then select which one gets triggered using a JS query, something like:

if(yourDropdown.value === "A"){
   return query1.trigger();
}else if(yourDropdown.value === "B"){
   return query2.trigger();
}

If that won't work for you and you absolutely have to have the table names be variable you can try creating a new SQL resource with prepared statements disabled that you can use to run the query.

Let me know if that helps!

1 Like
3

I see this example of prepared statements disabled:

select id, first_name, last_name
from users u
where u.id = '{{ numberInput1.value }}'

But instead of passing value for users.id, I want to pass value to the table name which is users.

So, in my case assuming I have dropdown with 2 options:

if dropdownValue equal A, then:
SELECT id, first_name, last_name FROM users_A

Else if dropdownValue equal B, then:
SELECT id, first_name, last_name FROM users_B

Can I achieve this?

4

@yeftanma
I think this can be achieved with some DECLARE/SET combos and a final EXEC in your query:

image
image982×606 35.3 KB

The values for the selection options just need to be set to your DB Table names

5

With prepared statements disabled, you should be able to use

SELECT id, first_name, last_name FROM {{ yourDropdown.value}}

Where yourDropdown has a list of table names to select from.

1 Like