Grafana Postgres ERROR when using Variables with ALL in WHERE clause multi value case when did not work

Grafana PostgreSQL
1

I have integrated Grafana Postgres Datasource for my reporting. For the report Generation, I have added Variables, for example, type. I have used the case when clause in this variable filter. case when type 0 then "ZERO" when type 1 then "ONE", when type 2 then "TWO" Variable filter works fine in the reporting. Again in the Postgres query where clause, I need to convert this to integer, case when type "ZERO" then 0 when type "ONE" then 1, when type "TWO" then 2 . When I select single value in the filter, ONE or TWO it works fine.
In the variable, there is an option to choose All and multi-value.
When I select All in the filter, it appends ZERO, ONE, TWO in the value. The query became case when type "ZERO, ONE, TWO" then 0
Here Postgres throws the error. How can I solve this issue?
Thank you team in advance.

2

Welcome

So this is really not a grafana error , it is a postgres query error. Nonetheless please post your postgre query that uses the variable?

3

Thank you for your comment.
Postgres works fine for this case. Variable ‘All’ selected gives all the values in one go “ZERO, ONE, TWO”. This is causing the issue. Is there any way to give the value one by one when All or multi-value is selected or we cannot use All and multi-value selection here?

4

Still waiting for you to post the query

5

Thank you for the reply.
Sample SQL query in the where condition with CASE WHEN Clause. When I select multi value, then it adds all the values to the same variable.

select CASE type
WHEN 0 THEN ‘ZERO’ WHEN 1 THEN ‘ONE’ WHEN 2 THEN ‘TWO’ END as “Type” from table where CASE type WHEN ‘ZERO’,‘ONE’,‘TWO’ = ‘ZERO’ THEN 0;

1 Like
6

In the variable setup there is an custom option you can provide when All is selected. For example you can ssy -1

Then you can specify that value of -1 in the WHERR CASE statement