Having "___ in (select distinct ___ from #temp)" in Case statement
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.
精彩评论