在where子句tsql中选择inside case语句

I have written a case condition inside where clause which is working fine without any sub queries, but it is not working with sub queries

我已经在where子句中编写了一个case条件，它在没有任何子查询的情况下运行良好，但是它不能处理子查询

for example

例如

declare @isadmin varchar(5) = 'M'

select * from Aging_calc_all a where a.AccountNumber in 
(case @isadmin when 'M' then 1 else 0 end)

which is working fine.

这是工作正常。

However this does not seem to work -

然而，这似乎并不奏效

select * from Aging_calc_all a where a.AccountNumber in 
(case @isadmin when 'M' then (select AccountNumber from ACE_AccsLevelMaster where AssignedUser=7) else 0 end)

Any suggestions or this is a t-sql bug in 2008.

任何建议或者这是2008年的t-sql错误。

3 个解决方案

#1

The error you're receiving is "Subquery returned more than 1 value", I believe. So you can't return multiple values after THEN. You should rewrite your query to something like this:

您接收到的错误是“Subquery return more than 1 value”，我相信。所以之后你不能返回多个值。您应该将查询重写为以下内容:

select 
    * 
from Aging_calc_all a 
where 
    (@isadmin='M' and a.AccountNumber in (select AccountNumber from ACE_AccsLevelMaster where AssignedUser=7))
    or 
    (@isadmin<>'M' and a.AccountNumber=0)

#2

select * from Aging_calc_all a where a.AccountNumber in (
   SELECT AccountNumber 
     from ACE_AccsLevelMaster 
     where AssignedUser=7 AND @isadmin = 'M'
   UNION ALL select 0 where @isadmin <> 'M'
   )

EDITED To show how use multiple criteria

编辑以显示如何使用多个标准

select * from Aging_calc_all a where a.AccountNumber in (
   SELECT AccountNumber 
     from ACE_AccsLevelMaster 
     where AssignedUser=7 AND @isadmin = 'M'
   -- case to select from another table:
   UNION ALL select * from ANOTHER_TABLE where @isadmin = 'A' 
   -- case to select from const set (1, 2, 3):
   UNION ALL select * from ( 
      select 1 union all select 2 union all select 3) x where @isadmin = 'B' 
   -- case to show how 'else' work
   UNION ALL select 0 where @isadmin not in( 'M', 'A', 'B')
   )

#3

This should work(although it's not a direct answer of the question):

这应该是可行的(尽管这不是问题的直接答案):

SELECT * 
FROM   aging_calc_all a 
WHERE  ( @isadmin <> 'M' AND a.accountnumber = 0 ) 
    OR ( @isadmin =  'M' AND a.accountnumber IN (SELECT accountnumber 
                                     FROM   ace_accslevelmaster 
                                     WHERE  assigneduser = 7) )

#1