Solved: Snowflake Unsupported subquery type cannot be evaluated

When you are migrating to Snowflake, eg. The below query from Oracle,

select t1.id,
	(
	  select listagg(value2, ',') within group (order by value2) as v2
	  from table2 t2
	  where t1.id = t2.id
          group by t1.id
	) v2 
from table1 t1;

Throws the error message:

SQL compilation error: Unsupported subquery type cannot be evaluated

Solution:

Add a LEFT JOIN and rewrite the SQL as shown below

select 
      t1.id,
      sub.v2
from table1 t1
left join (select t2.id, listagg(value2, ',') within group (order by value2) as v2
	  from table2 t2 group by t2.id) sub
on (t1.id = sub.id)
;

nVector

posted on 26 Jan 21