英文:
Snowflake sub query not supported
问题
我想运行一个查询，看看试验产品表中是否存在产品ID，以及该产品ID是否存在于折扣表中，然后基于此创建一个标志。
但是我遇到了错误"不支持的子查询类型"。
我的代码如下：
select a.*, b.address,
case when (a.product in ((select distinct product from trial_products)) and a.product not in ((select distinct product from discount_table))) then 'Indirect'
when (a.product in ((select distinct product from trial_product )) and a.product in ((select distinct product from discount_table))) then 'Direct'
else Null end as DirectIndirectFlag
from
salesTable a
left join
all_products b
on a.product= b.product
我如何通过嵌套查询来运行这个case when语句？我在这个查询中使用Snowflake。谢谢您的帮助。
英文:
Id like to run a query where i look to see if product ids exist from a trial products table, and also if this product id exists in a discounts table, and then create a flag based on this.
but im faced with the error 'unsupported sub query type'.
my code is as follows:
select a.*, b.address,
case when (a.product in ((select distinct product from trial_products)) and a.product not in ((select distinct product from discount_table))) then 'Indirect'
when (a.product in ((select distinct product from trial_product )) and a.product in ((select distinct product from discount_table))) then 'Direct'
else Null end as DirectIndirectFlag
from
salesTable a
left join
all_products b
on a.product= b.product
How can i run this case when statement by nesting a query? Im using snowflake for this query.
thanks for your help
答案1
得分: 1
你应该使用公共表达式（CTE）和连接来使这个工作正常。
with trial_p as (
select distinct product
from trial_products
), disc_p as (
select distinct product
from discount_table
)
select a.*, b.address,
case
when trial_p.product is not null and disc_p.product is null then '间接'
when trial_p.product is not null and disc_p.product is not null then '直接'
else Null
end as 直接间接标志
from salesTable a
left join trial_p on a.product = trial_p.product
left join disc_p on a.product = disc_p.product
left join all_products b on a.product= b.product
英文:
You should use CTE and joins to make this work.
with trial_p as (
select distinct product
from trial_products
), disc_p as (
select distinct product
from discount_table
)
select a.*, b.address,
case
when trial_p.product is not null and disc_p.product is null then 'Indirect'
when trial_p.product is not null and disc_p.product is not null then 'Direct'
else Null
end as DirectIndirectFlag
from salesTable a
left join trial_p on a.product = trial_p.product
left join disc_p on a.product = disc_p.product
left join all_products b on a.product= b.product
答案2
得分: 0
也许可以使用 EXISTS 并嵌套 CASE 以避免冗余的子查询：
select
a.*,
b.address,
case
when exists (select 1 from trial_products t where t.product = a.product) then
case
when exists (select 1 from discount_table d where d.product = a.product) then '直接'
else '间接'
end
else Null
end as 直接间接标志
from
salesTable a
left join
all_products b on a.product = b.product
英文:
Perhaps use EXISTS and nest your CASEs to avoid redundant subqueries:
select
a.*,
b.address,
case
when exists (select 1 from trial_products t where t.product = a.product) then
case
when exists (select 1 from discount_table d where d.product = a.product) then 'Direct'
else 'Indirect'
end
else Null
end as DirectIndirectFlag
from
salesTable a
left join
all_products b on a.product = b.product
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论