Search code examples
sqlmonetdb

Using WITH in MonetDB

I'm trying to execute the next query in MonetDB using "WITH":

with a as (select data_string from colombia.dim_tempo)
select 
 t.ano_mes 
,f.sg_estado
,f.cod_produto 
, sum(f.qtd_vendidas) as qtd_vendidas
, count(*) as fact_count
from colombia.fact_retail_market f, colombia.dim_tempo t
where f.cod_anomes = t.data_string 
and t.data_string in (a.data_string)
group by
 t.ano_mes 
,f.sg_estado 
,f.cod_produto ;

But always get this message:

enter image description here

What's wrong with the sentence?

Solution

  • The WHERE clause needs to be:

    WHERE f.cod_anomes = t.data_string AND
      t.data_string IN (SELECT data_string FROM a)

    That is, IN needs to be followed by a subquery against the CTE.