SQL query - Field Filter

Hi,

I have this SQL query:

select concept, count(*)
from annotation
where exists (select 1
              from annotation a2
              where a2.comment_commentid = annotation.comment_commentid and a2.concept = 'Fatigue'
             )
group by concept;

And I want to replace 'Fatigue' with {{word}}, to do a filter widget, maping to the column from database.
I have the following error:

ERROR: syntax error at or near "=" Position: 307

What I need to change to aplly the filter? selecting the available words from that column?
With variable type as Text it works... But don't display all the available options, in filter, as variable type Field Filter do...

Thanks!

Hi @renatojmsantos
Make sure you don't include column or operator, when using a Field Filter. Read more here:
https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html

if I use only {{word}} insted of a2.concept = {{word}}, it give results that i don't want...

How should I change the SQL query?

Thanks @flamber

@flamber if I do:
select concept, count(*) from annotation where exists (select 1 from annotation a2 where a2.comment_commentid = annotation.comment_commentid and {{value}} ) group by concept;
It give {{value}} from annotation and not from a2.... and that is not what I want.

What I need to change to aplly the filter? selecting the available words from that column? Or it is a issue and not possible to do?
With variable type as Text it works... But don't display all the available options, in filter, as variable type Field Filter do...

Thanks

@renatojmsantos Please read the gotchas section: https://www.metabase.com/learn/building-analytics/sql-templates/field-filters.html#field-filter-gotchas

You currently cannot use Field Filters with table aliases, so you would have to change your SQL to this:
select a1.concept, count(*) from annotation a1 where exists (select 1 from annotation where annotation.comment_commentid = a1.comment_commentid and {{value}} ) group by a1.concept;