In Metabase, one can add variables with {{variable}} in queries, such as:
select *
from dashboard_demographic_view
where clinic_id = {{clinic}}
, where dashboard_demographic_view is a view created in advance. In the query above, rows where clinic_id matches what is given in {{clinic}} will be returned.
Still, I am curious what if I need that filter only when I give {{clinic}} a value, or otherwise the query should return all the rows. I've referred to SQL add filter only if a variable is not null but it is not helping in my case.
select *
from dashboard_demographic_view
where ({{clinic}} is null or clinic_id = {{clinic}})
The query above gives Unable to substitute 'clinic': param not specified. Found: ("clinic") when I don't specify clinic.
Theoretically this can be solved by making the variable {{clinic}} a field filter. However, for some reasons that cannot be done for a view (#8800 in metabase).
I am seeking workarounds other than making a real table to fulfill my purposes. Any help would be appreciated.
Would optional clauses help you here? See the end of Metabase Documentation: Sql Parameters.
Your code would essentially become something like
select *
from dashboard_demographic_view
[[where clinic_id = {{clinic}})]]
(This question has probably been asked earlier too – maybe worth checking if there are duplicates inside Stack Overflow.)