Search code examples
google-bigquerygrafana-variable

Bigquery to handle the empty input values with IN operator

I have created a dashboard in Grafana and integrated with Bigquery as a datasource. I have a simple select query to get the count of records from the database which works fine when user selects values from dropdown but when user misses to select the value from dropdown and hits search command I am running into an exception rather getting 0 as a default value.

Below the query I am using for getting the count, when user selects some value the query constructs like below

SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME IN ('karthik','java','test')

but when user missed to select value and hits enter the query is being formed as below

SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME IN ()

and I am getting error as Syntax error: Unexpected ")"

Can someone help me with the bigquery select query to handle my situation. The query I am looking for will have to get the records when user passes single or multiple employee name and at the same time it has handle the situation where user doesn't pass any value.

Solution

  • Try below instead.

    SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME IN UNNEST(['karthik','java','test'])

    IN UNNEST() will work for an empty array.

    SELECT * FROM EMPLOYEE WHERE EMPLOYEE_NAME IN UNNEST([])