Snowflake子查询不受支持。

huangapple
go评论98阅读模式
英文:

Snowflake sub query not supported

问题

我想运行一个查询，看看试验产品表中是否存在产品ID，以及该产品ID是否存在于折扣表中，然后基于此创建一个标志。
但是我遇到了错误"不支持的子查询类型"。

我的代码如下：

  1. select a.*, b.address,
  2.   case when (a.product in ((select distinct product from trial_products)) and a.product not in ((select distinct product from discount_table))) then 'Indirect' 
  3.   when (a.product in ((select distinct product  from trial_product )) and a.product in ((select distinct product from discount_table))) then 'Direct'
  4.   else Null end as DirectIndirectFlag
  5.   from 
  6. salesTable a
  7. left join 
  8. all_products b 
  9. 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:

  1. select a.*, b.address,
  2.   case when (a.product in ((select distinct product from trial_products)) and a.product not in ((select distinct product from discount_table))) then 'Indirect' 
  3.   when (a.product in ((select distinct product  from trial_product )) and a.product in ((select distinct product from discount_table))) then 'Direct'
  4.   else Null end as DirectIndirectFlag
  5.   from 
  6. salesTable a
  7. left join 
  8. all_products b 
  9. 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）和连接来使这个工作正常。

  1. with trial_p as (
  2.   select distinct product 
  3.   from trial_products
  4. ), disc_p as (
  5.   select distinct product 
  6.   from discount_table
  7. )
  8. select a.*, b.address,
  9.   case 
  10.      when trial_p.product is not null and disc_p.product is null then '间接' 
  11.      when trial_p.product is not null and disc_p.product is not null then '直接'
  12.      else Null 
  13.   end as 直接间接标志
  14. from salesTable a
  15. left join trial_p on a.product = trial_p.product
  16. left join disc_p on a.product = disc_p.product
  17. left join all_products b on a.product= b.product
英文:

You should use CTE and joins to make this work.

  1. with trial_p as (
  2.   select distinct product 
  3.   from trial_products
  4. ), disc_p as (
  5.   select distinct product 
  6.   from discount_table
  7. )
  8. select a.*, b.address,
  9.   case 
  10.      when trial_p.product is not null and disc_p.product is null then 'Indirect' 
  11.      when trial_p.product is not null and disc_p.product is not null then 'Direct'
  12.      else Null 
  13.   end as DirectIndirectFlag
  14. from salesTable a
  15. left join trial_p on a.product = trial_p.product
  16. left join disc_p on a.product = disc_p.product
  17. left join all_products b on a.product= b.product

答案2

得分: 0

也许可以使用 EXISTS 并嵌套 CASE 以避免冗余的子查询：

  1. select 
  2.     a.*, 
  3.     b.address,
  4.     case 
  5.         when exists (select 1 from trial_products t where t.product = a.product) then
  6.             case
  7.                 when exists (select 1 from discount_table d where d.product = a.product) then '直接'
  8.                 else '间接'
  9.             end 
  10.         else Null 
  11.     end as 直接间接标志
  12. from 
  13.     salesTable a
  14.     left join 
  15.     all_products b on a.product = b.product
英文:

Perhaps use EXISTS and nest your CASEs to avoid redundant subqueries:

  1. select 
  2.     a.*, 
  3.     b.address,
  4.     case 
  5.         when exists (select 1 from trial_products t where t.product = a.product) then
  6.             case
  7.                 when exists (select 1 from discount_table d where d.product = a.product) then 'Direct'
  8.                 else 'Indirect'
  9.             end 
  10.         else Null 
  11.     end as DirectIndirectFlag
  12. from 
  13.     salesTable a
  14.     left join 
  15.     all_products b on a.product = b.product

huangapple
  • 本文由 发表于 2023年2月24日 00:31:13
  • 转载请务必保留本文链接：https://go.coder-hub.com/75547666.html
  • snowflake-cloud-data-platform
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定