How to use CTEs with Field Filters

Get Help
1

I have my data in different folders and I have written a query utilizing common table expressions to pull data from the different collections and consolidate it into one table within the query.

Now, I want to use a dropdown to filter this new created table but Metabase throws an error of 'missing from clause' for the table, probably because I am using an alias to filter it.

Is there an alternative solution to solving this problem?

Example:

cte1 as (select * from "students"."records"),
cte2 as (select * from "employees"."records")

select * from cte1 union all select * from cte2
where name = {{name filter}}

2

The problem I that the field filter will be transformed to a query pointing to a single table, and that’s where it will fail.

Remember, field filters will always be transformed into queries