WHERE 东西在(CASE WHEN 语句)? [英] WHERE Something IN (CASE WHEN statement)?

问题描述

我想根据条件写一个select子句"！但我有错误:

I want to write a "select clause" according to conditional condition! bu I have error:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我该如何解决??
这是我的简化代码:

How can I fix it??
here is my simplified code:

SELECT  UnitsAllocation.UnitID
        , OrganizationUnits.Title AS UnitTitle
        , 'Title' AS ExpenseTitle1
        , SUM(UnitsAllocationDetails1.ExpenseAmount1) AS ExpenseAmount1 
FROM    [bdg_UnitsAllocation] UnitsAllocation 
        LEFT OUTER JOIN (
          SELECT  UnitsAllocationDetails.UnitsAllocationID
                  , SUM(UnitsAllocationDetails.Amount) / 1 AS ExpenseAmount1  
          FROM    [bdg_UnitsAllocationDetails] UnitsAllocationDetails  
          WHERE   UnitsAllocationDetails.ExpenseID IN (
                    CASE 1 WHEN 1 
                    THEN ( SELECT Id FROM bdg_Expenses WHERE ParentId = 1 ) 
                    ELSE ( SELECT Id FROM bdg_Expenses WHERE Id = 1 )  
                    END
                  )  
          GROUP BY 
                  UnitsAllocationDetails.UnitsAllocationID
        ) UnitsAllocationDetails1 ON UnitsAllocationDetails1.UnitsAllocationID = UnitsAllocation.ID 
        LEFT OUTER JOIN [bdg_OrganizationUnits] OrganizationUnits ON UnitsAllocation.UnitID = OrganizationUnits.ID 
GROUP BY 
        UnitsAllocation.UnitID, OrganizationUnits.Title 

请看CASE"和IN"语句.

推荐答案

为什么要使用案例?你不能就这样

why use a case? can't you just do

where (@Level = 1 and ExpenseId in (select id from bdg_expenses where parentid = 1)) or
      (@Level <> 1 and ExpenseId in (select id from bdg_expenses where id = 1))

这篇关于WHERE 东西在(CASE WHEN 语句)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！