开发者

Having "___ in (select distinct ___ from #temp)" in Case statement

开发者 https://www.devze.com 2023-01-17 14:49 出处:网络
I\'m trying to achieve this select case when Org_CD = \'1111\' or Org_CD in (select distinct New_Org_CD from #temp) then \'International\'

I'm trying to achieve this

select
   case
      when Org_CD = '1111' or Org_CD in (select distinct New_Org_CD from #temp) then 'International'
   end as 'Organisation',
count(*)
from #AnotherTempTable
group by
   case
      when Org_CD = '111开发者_Python百科1' or Org_CD in (select distinct New_Org_CD from #temp) then 'International'
   end 

I received this error:

Column '#AnotherTempTable.Org_Cd' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Is it because I cannot use "in" keyword inside the case statements? If yes, any known workarounds would be more than helpful!


I'd try this...

select
    Org_CD, count(*)
from
    #AnotherTempTable A
    JOIN
    (select distinct New_Org_CD from #temp UNION SELECT '1111') T ON A.Org_CD = T.New_Org_CD
group by
   Org_CD

You can't have an inline IN like this (CASE + aggregate)

If this is not OK, please give sample data and output


I solved it with a variation of gbn's solution using 'Union'. Thanks all.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号