26 Jan 21 ·  nVector in Snowflake ·        Bookmark ·  Report ·  More actions.. Lock comments ·  Pin thread

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

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds