SQL compilation error: Unsupported subquery type.

Read Time:2 Minute, 33 Second

Snowflake provides rich support of subqueries. But, some scalar subqueries that are available in the relational databases such as Oracle are not supported in Snowflake yet. However, We have to identify the alternate methods for such a subqueries. In addition ,Snowflake does not support correlated subquery in the select clause and reports unsupported subquery error.

The Snowflake database doesn’t support correlated subqueries as extensively as Oracle does.
You have to find a way to rewrite, eg. using

WITH <common table expressions ...>
SELECT ...
JOIN ...

create table s_order(order_id varchar(10),order_name varchar(10),order_status varchar(10))
create table order1(order_id varchar(10),sales_name varchar(10),order_status varchar(10))
insert into s_order(order_id,order_name,order_status)
values
('1','A','Active'),
('2','B','Active'),
('3','C','Active'),
('4','D','Active'),
('5','E','Inactive'),
('6','F','Inactive')
insert into order1(order_id,sales_name,order_status)
values
('1','A','Active'),
('2','B','Active'),
('3','C','Active'),
('4','D','Inactive')

Executing the subquery in snowflake and it has resulted the error: Unsupported subquery type cannot be evaluated”

select b.order_id "ID",(select o1.sales_name from order1 o1 where b.order_id = o1.order_id ) "Name"
from s_order b

Snowflake may release solution for these types of subqueries in the future. But, at the moment, the only solution is to rewrite the query.

Now Rewrite Subquery as :

Approach 1:
select 
t1.order_id,a1.sales_name
from s_order t1 inner join (select sales_name,order_id from order1 t2) a1
on (t1.order_id = a1.order_id)

Approach 2:
select b.order_id "ID",(select ANY_VALUE(o1.sales_name ) sales from order1 o1
 where b.order_id = o1.order_id ) "Name" from s_order b

Approach 3:
select t1.order_id,t2.sales_name from s_order t1 inner join order1 t2 
on (t1.order_id = t2.order_id)

How the update works in scalar queries:
Moreover, Using Snowflake and trying to update a column from Table 1 with the same records from Table 2 for
each user – there’s only 1 of these values per use in Table 2. This works in Oracle, but what does
Snowflake need to get this working?

update s_order a
set order_status = (select order_status from order1 b where a.order_id = b.order_id)
where exists
(select '1' from order1 b where a.order_id = b.order_id)

How to fix:
update s_order a
set order_status = (select a1.sales_name from s_order t1 inner join 
 (select sales_name,order_id from order1 t2 limit 1 ) a1
 on (t1.order_id = a1.order_id))
where exists
(select '1' from order1 b where a.order_id = b.order_id)