FROM database_name as db >>> FROM {{select_table.value}} as db

[MarkAC007]

Greetings one and all.

I am trying to transform the following working query

SELECT 
 key,
 id, 
 value
FROM database_name as db
WHERE db.id = {{recordid.value}}

in to something like the following where the table is being selected from the Select component.

SELECT 
 key,
 id, 
 value
FROM {{select_table.value}} as db
WHERE db.id = {{recordid.value}}

I am getting the following error.

---

getcontrol failed (1.069s):syntax error at or near "$1"
getcontrol
from getcontrol response(getcontrol)
in getcontrol.trigger()(getcontrol)

1. select_table.value: (3) [Object, Object, Object]
   input_control_id.value: "AC-2"environment: "production"
   from editor update

---

The objective is to be able to select the table to fetch the record ID from.

Any advise would be welcome.

[kevinwhinnery]

Hey there! It looks like select_table.value might be returning an array versus a single value. Are you certain that select_table is a reference to a Select component?

[kevinwhinnery]

Another possible issue at play might be that you can't use dynamic table or column names by default - see this post: How do I write a SQL UPDATE with dynamic columns

[MarkAC007]

It looks like the Select component is returning the correct value however the value is wrapped in quotes. This can be seen in the image below. Any idea how to approach this?

[MarkAC007]

Some more context here. This is the following error I keep hitting.

[victoria]

Hey @MarkAC007! Just to double check, do you have prepared statements enabled in your resource?

https://docs.retool.com/docs/sql-queries#dynamic-table-names

[MarkAC007]

Hello, Yes, I do have them enabled and did also disable them and updated the query but it yielded the same results unfortunately. Maybe my approach is wrong?

Any input from the retool team or community would be greatly appreciated.

[sesgoe]

Hey @MarkAC007,

I helped someone out on the Retool Discord earlier today, and he was having similar issues, so here's a list of the things we checked and tried before getting things working:

• Make sure your resource (looks like a Postgres DB) has the Disable converting queries to prepared statements option turned ON
• try using a different table alias name instead of db, because db is technically a reserved word in postgres
• try not using a table alias since you know the column is the same regardless of table
• make sure your values are wrapped in quotes if they are string types
• fully restart chrome after changing the prepared statements setting to ensure prepared queries are DISABLED. All of the errors you've posted so far indicate that prepared queries are still enabled. This approach will definitely not work with prepared queries turned on.

Here's a screenshot of working minimal reproduction of your setup. I'm using the Retool DB which is also Postgres to do this:

The full query contents raw are:

select * from {{table_name_input.value}}
where {{id_column_input.value}} = '{{id_column_value_input.value}}'