0

I changed my sql request according which widget value is selected. I correct this request according my question enter link description here This following my request:

SELECT "LRU", "Client", extract(month from "Facturation") as mt


 CASE {{w_widget6.selectedValue}} 

                    WHEN {{w_widget6.selectedValue}}=1 THEN 
                        (select avg("Montant_fac_eur") as c1 from "foundry_sync"."data" where "month" between 1 and 6)

                 when {{w_widget6.selectedValue}} =2 THEN 
                        (select avg("Montant_fac_eur") as c2 from "foundry_sync"."data" where "month" between 2 and 7)

                 when {{w_widget6.selectedValue}} =3 THEN
                        (select avg("Montant_fac_eur") as c3 from "foundry_sync"."data" where "month" between 3 and 8)

                 when {{w_widget6.selectedValue}} = 4 THEN 
                        (select avg("Montant_fac_eur") as c4 from "foundry_sync"."data" where "month" between 4 and 9)

                 when {{w_widget6.selectedValue}} =5 THEN
                        (select avg("Montant_fac_eur") as c5 from "foundry_sync"."data" where "month" between 5 and 10)

                 when {{w_widget6.selectedValue}} =6 THEN 
                        (select avg("Montant_fac_eur") as c6 from "foundry_sync"."data" where "month" between 6 and 11)

                 when {{w_widget6.selectedValue}} =7 THEN
                        (select avg("Montant_fac_eur") as c7 from "foundry_sync"."data" where "month" between 7 and 12)

 END;

from "foundry_sync"."data"


group by "LRU", "Client"

But always I have the same error:

ERROR: syntax error at or near "CASE"
  Position: 68

I'm using foundry-postgate like source of my data into Palantir Cloud. Can you tell me where's my fault ?

vero
  • 1,005
  • 6
  • 16
  • 29
  • 1
    If we assume this is your exact SQL... Missing comma after mt. Select... `extract(month from "Facturation") as mt,` – xQbert Aug 09 '17 at 15:24
  • I added it now, it return this error: ERROR: column "LRU" does not exist Position: 8 – vero Aug 09 '17 at 15:25
  • Possible duplicate of [How to do an SQL request using CASE](https://stackoverflow.com/questions/45589804/how-to-do-an-sql-request-using-case) – yanman1234 Aug 09 '17 at 15:26
  • is the column LRU in the schema.table foundry_Sync.data? and is w_widget6.selected value wait maybe you just need to remove the ; after the `end` of the case. – xQbert Aug 09 '17 at 15:28
  • @xQbert yes I have the column LRU, and the w_widget6.selectedvlue also, I tested it before it works well. – vero Aug 09 '17 at 15:42
  • you still have a ; at the end of the case statement END; which causes the query to stop short of the from clause. – xQbert Aug 09 '17 at 15:49

1 Answers1

1

Personally I'd refactor the whole query:

  • Assumption being w_widget6.selectedvalue is a variable being passed in
  • month is a column in foundry_Sync.data
  • only need to use backticks on reserved words.
  • month needs to be in the group by.

. 

SELECT LRU
     , Client
     , extract(month from Facturation) as mt
     , avg("Montant_fac_eur") AVG_MONT_FAC_EUR
FROM foundry_sync.data FSD
WHERE `month` between 0+{{w_widget6.selectedValue}} and 5+{{w_widget6.selectedValue}}
GROUP BY LRU, Client, extract(month from Facturation)
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • xQbert thank you for your very good answer, I was thinking about this solution to add each time +5 to recovre the last month. I'll test it tomorrow when I'll be to office. Really you are a "Logical problem solver" – vero Aug 09 '17 at 18:18
  • 1
    Sometimes people want the answer to the question (which only addresses a symptom of the problem) some times people want the answer to the problem. I took a guess that refactoring may address the problem not the symptom. I'm a big ITIL/ITSM (IT Service Management) fan. Fix the problem you stop having to fix incidents. – xQbert Aug 09 '17 at 21:16
  • Really I don't have another words to add them! hats off to you – vero Aug 10 '17 at 08:39