Using field filter in sub query with UNION

Get Help
1

Hello

I have a nested query with several where conditions that are all put together using UNION. I want to be able to select the date using field filter date range.

     select event, bundle_count from (select count(*) as bundle_count, 'CREATED' as event, 1 as ordering from analytics where [[{{created_at}}]]
union
    select count(*) as bundle_count, 'AUTHENTICATED' as event, 2 as ordering from analytics where (error_code IS NULL OR error_code != 'ERR_2002') [[ AND {{created_at}}]]
union
    select count(*) as bundle_count, 'ACTIVATED' as event, 3 as ordering from analytics where starts_at IS NOT NULL [[AND {{created_at}}]])
tmp
order by ordering;

What am I doing wrong?

2

On this first line you have
where [[{{created_at}}]]

Note that as the where keyword is outside of the [[ ]], it will be included in the code at runtime even if there is no date given in the filter, resulting in incorrect SQL syntax.

See Optional Clauses in the documentation: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters

I think what you want is to write is:
[[where {{created_at}}]]
similar to how you’ve written [[ AND {{created_at}}]]

3

Thanks for the suggestion but I couldn’t get it working. I’m new to Metabase and SQL, maybe I don’t even need to use optional clause. My wished outcome is that Metabase takes the value of the date from the field filter and returns data according to the conditions expressed in the query.

Finally I want to create a funnel that is possible to filter by date and shows the amount of transactions in each step.

Funnel steps:

  1. Created
  2. Authenticated
  3. Activated

select event, bundle_count from (select count() as bundle_count, ‘CREATED’ as event, 1 as ordering from analytics [[where {{created_at}}]]
union
select count() as bundle_count, ‘AUTHENTICATED’ as event, 2 as ordering from analytics where (error_code IS NULL OR error_code != ‘ERR_2002’) [[ AND {{created_at}}]]
union
select count(*) as bundle_count, ‘ACTIVATED’ as event, 3 as ordering from analytics where starts_at IS NOT NULL [[AND {{created_at}}]])
tmp
order by ordering;

4

Your SQL is all over the place. You need to include * in the counts or it doesn’t know what you’re trying to count.
The ordering isn’t necessary as it will return in the order of the unions.
The subquery isn’t necessary.
You don’t need to alias every column after the first (not important though)
This is from the sample database, but achieves the same thing:

select count(*) as bundleCount, 'Created' as Event from orders where 1=1 [[and CREATED_AT > {{startdate}}]]
UNION 
select count(*), 'Authenticated'  from orders where PRODUCT_ID = 14  [[and CREATED_AT > {{startdate}}]]
UNION
select count(*), 'Activated' from orders where PRODUCT_ID = 10  [[and CREATED_AT > {{startdate}}]]

Note the 1=1 to avoid the empty WHERE clause.

If in doubt, test one clause at a time.

Not sure what your database is, but see if there’s a better tool to write the query in (without the variables), then copy into Metabase to add the variables.

5

hey andrew, as all the columns are coming from same table “orders” so we can refer the field filter{{startdate}} to 1 column. But if all the columns are coming from 3 different tables then how to refer the field filter in that situation.

6

Time to use a View.

7

to create view i need to have admin access(correct me if m wrong) which i dont have…is there any other way.

8

If you can add a new database to the server, you could use a linked database to generate the view.
Otherwise ask an admin to create the view.