Snowflake error, Unsupported subquery type cannot be evaluated

Question

I have a user defined function which returns number, argument is also a number. There is an aggregate function(sum()) inside the UDF. I am calling this UDF from a select clause. It's giving me the error "Unsupported subquery type cannot be evaluated".

Basically i am rewriting the query used in SQL server DB. But it seems like snowflake doesn't support this case as it is. How can i rewrite it for snowflake? The queries loos like:

Query:

select salesNumber, date , totalValue(SalesId) from salesTable

totalValue UDF body:

select sum(amount) from SalesItems where SalesId=parameter_salesId

display a sample output and a minimum amount of reproducible code. — Prathik Kini
– Prathik Kini, Commented Sep 5, 2024 at 18:09
@Srekanth can you please add the code for the function that you written. — Himanshu Kandpal
– Himanshu Kandpal, Commented Sep 5, 2024 at 19:56

marc_s · Accepted Answer · 2024-11-13 20:44:03Z

Yes, it is a correlated subquery and they perform badly, so write it better, the simpler way would be:

with as_cte as ( select SalesId, sum(amount) as s_amount from SalesItems group by 1 ) select st.salesNumber, st.date, c.s_amount from salesTable as st join as_cte as c on c.SalesId = st.parameter_salesId

If you have large tables that CTE would benefit from having filters added to it.

A horrible (for large table performance) way would be:

select st.salesNumber, st.date, sum(si.amount) as s_amount from salesTable as st join SalesItems as si on si.SalesId = st.parameter_salesId group by 1

I am rewriting SQL Server query for snowflake. I have to use UDF otherwise i have to rewrite lot of queries. Not much worried about performance, as it use once in a while. Is there a way to use UDF instead of CTE?

Collectives™ on Stack Overflow

Snowflake error, Unsupported subquery type cannot be evaluated

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related