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!
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.
If it isn't possible, I'll try with the CASE statement as you suggested.
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 {{ }}.
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 ?
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...
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.
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
@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!