0

I have created a Grafana dashboard with filter variables enabled along with "All" option enabled. I am working on BigQuery sql query to fetch the data.

I have constructed below query to fetch the data when "All" option is selected.

SELECT count(*) AS FAILED_COUNT FROM BG_TABLE where COLUMN1 like '%'.

with the above query I am able to fetch the data when user selects "All" option from dropdown but it fails when user selects multiple options from dropdown as the query is constructed like below

SELECT count(*) AS FAILED_COUNT FROM BG_TABLE where COLUMN1 like 'COLUMN_VALUE_1','COLUMN_VALUE_2'.

So to handle multiple options, I have updated query with "in" operator as below

SELECT count(*) AS FAILED_COUNT FROM BG_TABLE where COLUMN1 in ('COLUMN_VALUE_1','COLUMN_VALUE_2')

This resolved the problem with multiple options but failed when I select "All" option as the query is constructed like below

SELECT count(*) AS FAILED_COUNT FROM BG_TABLE where COLUMN1 in ('%')

Can someone help me with the query to handle both "All" and multiple options selection.

Karthik Peddineni
  • 81
  • 4
  • Did you set the `Custom all value` of the variable to `%` or why is it interpolated like that? – dnnshssm Mar 07 '23 at 14:32
  • I saw somewhere in Google to pass % to fetch all records using Like operator, I have removed it and set to default blank and update variable query with not null condition and the issue got resolved – Karthik Peddineni Mar 08 '23 at 00:51

1 Answers1

0

Set the Custom All value default to blank and in variable query include NOT NULL condition like below

Select Name from employee where Name IS NOT NULL.
Karthik Peddineni
  • 81
  • 4