How do I create conditional queries using field filters in queries?

Get Help
1

I'm attempting to create a query that returns a different sub queries depending on if the field filter is empty or not. This is my query:

select case when {{installed_at}} != null
then (select 1)
else
(select 2) end

I get this error when I try to run it with something selected in the filter box:

ERROR: invalid reference to FROM-clause entry for table "adjust_installs" Hint: There is an entry for table "adjust_installs", but it cannot be referenced from this part of the query.

When I run the filter without a filter selected the query runs fine and returns 2

The column the field filter references is a creation timestamp and the field filter widget is a date filter.

2

Hi @Alexhs
You cannot use Field Filters outside of WHERE-clause:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html
You would have to use simple variable types from something like that.

1 Like
3

Hi @flamber, thaks for the tip, sorry for the slow reply was having a play around I couldn't get anything to work though.
The query will be part of a dashboard that has a date field filter so this query would not work with the dashboard if it uses a simple variable type

4

@Alexhs Try posting a real query, so it's easier to understand the context.
It will work on dashboards, but you would have to update the connected filter, since the dashboard doesn't automatically update if you just change the variable type on the question.

5

This would be the full query

select case when {{installed_at}} = null
then (select 64979 + count(*) as "count" from third_party_data.installs)
else (select count(*) as "count" from third_party_data.installs 
          where {{installed_at}}) 
end

where installed_at data is a field filter referencing an installation timestamp column in the "installs" table.

As to you saying it should work - I tried doing a query like:

select 64979 + count(*) as "count"
from third_party_data.installs
where installed_at = {{installed_at}}

but when I try to apply the dashboard filter to that card it says "no valid fields". The simple variable type is set to "date"

6

@Alexhs
I don't understand your query, but this should work:
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#setting-complex-default-values-in-the-query

select
  [[(select count(*) from third_party_data.installs where {{installed_at}}) --]] (select 64979 + count(*) from third_party_data.installs)
  as "special count"

If you are using Date variable, then you can only select Single Date on the dashboard.

1 Like
7

The query is trying to correct for missing historical data by adding the total count of rows missing (64979). so I wanted to create a query that returns the total including the missing amount if a date range filter is not applied and then just return the count of rows for the selected date range if the date range filter is applied.

Thanks for the query! that's a clever trick that will be helpful in the future

If you are using Date variable, then you can only select Single Date on the dashboard.

Thats the other problem that the dashboard uses the all option date selector and not a single date. But you're query worked anyway?! I put the card in the dashboard and it works as I originally wanted it to so thanks a lot :slight_smile: