Dynamically column name in a query

Queries and Resources
1

Here is my current SQL statement =

select t.*, m."5_26_2023" AS letter_number
FROM test_mailing_info t
JOIN mailing_dates m ON (t.collection_id || t.batch) = m.batch2

I want to make the column name "5_26_2023" dynamic according to the selected value of a select component. I have mapped column names from the table 'mailing_dates' to a select component called 'select1'

I tried doing this ->
select t.*, m.{{ select1.value }} AS letter_number
FROM test_mailing_info t
JOIN mailing_dates m ON (t.collection_id || t.batch) = m.batch2

but I get the error "syntax error at or near "$1""

How can I make the column name dynamic on this SQL query?

2

Hello,

Retool has a feature called prepared statements. It's essentially putting quotes around the any output from {{ }}. Please take a look at this documentation: https://docs.retool.com/docs/sql-queries#dynamic-table-names

You can turn it off within the advance section of each resource. Please thoroughly understand the risk when turn it off.

image
image699×191 16.9 KB

1 Like
3

thank you @lamh_bytecode.io , i have already seen this answer in my researches, but i can't find this checkbox (i am admin/only one user of my app)
can you tell me please where i can find it?

4

@Emmanuelle_S that checkbox is under each individual resource settings. Edit a resource and you will see at the bottom.