Use select value in SQL statement

Hello,
I'm trying to use the value from a Select component into my query.
I can't get it to work.
See attached screenshot.


My ORDER BY statement is supposed to get its value from a select component, but instead of getting the value itself, it gets it as a string.
Thanks in advance for any help!

Hey @loicchabut35, welcome to the community!

The ORDER BY clause should refer to a field in your SELECT clause. You may wish to familiarise yourself with the ORDER BY clause: SQL ORDER BY Keyword

In your case, you would want to do ORDER BY SUM(tous.Montant) followed by either ASC (ascending) or DESC (descending).

If you want to change the ORDER BY clause dynamically (I guess this is why you have an order by select component?), you could use a CASE statement like this:

ORDER BY
   CASE
      WHEN {{orderBy.value}} = “Somme” THEN SUM(tous.Montant)
      WHEN {{orderBy.value}} = “someOtherString” THEN someOtherColumn
   END
   ASC|DESC

Hello @ryanm, thanks for your answer!
I have a "AS" keyword in my select statement (AS Somme). Shouldn't it work?
Here is a screenshot of my select component. I thought it would work to affect values to the labels of the select component like that.
image
If it isn't possible, I'll try with the CASE statement as you suggested.

Hello @loicchabut35

The reason it doesn't work because of a safety feature in Retool called Prepared Statement.

What it does is automatic put quotes around {{ orderBy.value }} so your SQL is now become

SELECT SUM(tous.Montant) AS Somme, tous.Type
.....
ORDER BY 'Somme'

And you have seen the effect of it.

My ORDER BY statement is supposed to get its value from a select component, but instead of getting the value itself, it gets it as a string.

I don't recall you mentioned which dialect your SQL is in but in PostgreSQL, it will fail as below. Note, the quotes around ticket_id as that is what prepared statement will do for all results in {{ }}.
image

You can turn it off but please read the doc linked above to thoroughly understand the risks of it.

Thank you @lamh_bytecode.io , this is effectively the problem.
My database is in google sheets... not a real database.
From what I understand, the risk of disabling Prepared Statement is that it will not protect my database from SQL injection.
From what I understand from SQL injections, if my retool app is shared only with specific trusted people, I have nothing to fear, right ?

And, @lamh_bytecode.io , by the way, I can't find how to disable prepared statements on Google Sheets Resources... :thinking:

I tried removing the two quotes added by the Prepared Statement protection (ORDER BY SUBSTRING({{ orderBy.value }},1,LEN({{ orderBy.value }})-2);, I also tried with LEFT and RIGHT SQL functions), but it doesn't work...

@loicchabut35

My apologies, I overlooked, your original image is from Query JSON with SQL. Unfortunately, the case statement provided by ryanm is your best solution for Query JSON with SQL. Query JSON with SQL is using a JS library called AlaSQL I believe. It has limitation and is not a full-fledged SQL language.

1 Like

Thank you @lamh_bytecode.io and @ryanm for your answers.
Unfortunately, I can't get it to work...
I tried without the CASE statement, it works fine, but I can't get the CASE statement to work.
This works fine:

ORDER BY
  Somme

But this doesn't work:

ORDER BY
   CASE
      WHEN {{orderBy.value}} = 'Somme' THEN SUM(tous.Montant)
      WHEN {{orderBy.value}} = 'Categorie' THEN tous.Type
   END asc

(as you can see in screenshot below)


I really don't understand why...!

@loicchabut35
Can you try one last change? Change to below

ORDER BY
   CASE
      WHEN {{orderBy.value === 'Somme'}} THEN SUM(tous.Montant)
      WHEN {{orderBy.value === 'Categorie'}} THEN tous.Type
   END asc

Essentially when true sort by this value. I simulated and it works as below.
order by

1 Like

@lamh_bytecode.io
I found out what the problem was: tous.Type wasn't allowed / understood in the CASE statement.
I should say I was getting frustrated because you were showing me that it was working on your side!

1 Like

@loicchabut35
I'm glad you found out why...