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
3
Yes, it is a corrolated subquerey 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 benifit 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
1