在查询中返回多个值的子查询

Question

我正在尝试创建一个视图，该视图包含执行计算以确定要生成哪个字段的部分。但是，我遇到了子查询的问题。

每当我试图执行的观点，我收到错误消息：Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

我明白了什么是说明，通常我会用WHERE子句和子查询中的参数解决这个问题。但是，由于我需要将此视为一种观点，因此我如何缓解此问题？我已经为多个案例独立运行子查询，并验证他们确实每个订单返回一行。是否有可能让子查询仅从当前行中的单个结果中选择它在视图中？

这是视图的特定部分的代码片段。任何建议，你们可以提供将不胜感激。

SELECT 
    CASE 
     WHEN ORC.SimultaneousCalculation = 1 
      THEN CASE 
        WHEN -- Select the Owner's Simultaneous Report Code when Sales Price >= Loan Amount. 
         (SELECT 
          CASE 
           WHEN (ISNULL(O.OwnerPolicyLiability, 0) = 0) 
            THEN O.SalesPrice 
           WHEN (ISNULL(O.OwnerPolicyLiability, 0) > 0) 
            THEN (O.OwnerPolicyLiability) 
           ELSE 0 END AS 'SalesPrice' 
           FROM 
            Orders O) >= 
         (SELECT 
          CASE 
           WHEN (ISNULL(L.LoanPolicyLiability, 0) = 0) 
            THEN L.Amount 
           WHEN (ISNULL(L.LoanPolicyLiability, 0) > 0) 
            THEN (L.Amount) 
           ELSE 0 END AS 'LoanAmount' 
           FROM 
            Orders O 
            INNER JOIN Loan L 
             ON O.OrdersID = L.OrdersID 
             AND L.LoanOrder = 1) 
         THEN CONVERT(VARCHAR(MAX),RES.SalesPolicyCode) 
        WHEN -- Select the Owner's Simultaneous Report Code when Sales Price < Loan Amount. 
         (SELECT 
          CASE 
           WHEN (ISNULL(O.OwnerPolicyLiability, 0) = 0) 
            THEN O.SalesPrice 
           WHEN (ISNULL(O.OwnerPolicyLiability, 0) > 0) 
            THEN (O.OwnerPolicyLiability) 
           ELSE 0 END AS 'SalesPrice' 
           FROM 
            Orders O) < 
         (SELECT 
          CASE 
           WHEN (ISNULL(L.LoanPolicyLiability, 0) = 0) 
            THEN L.Amount 
           WHEN (ISNULL(L.LoanPolicyLiability, 0) > 0) 
            THEN (L.Amount) 
           ELSE 0 END AS 'LoanAmount' 
           FROM 
            Orders O 
            INNER JOIN Loan L 
             ON O.OrdersID = L.OrdersID 
             AND L.LoanOrder = 1) 
         THEN CONVERT(VARCHAR(MAX),RES.LoanPolicyCode) 
        ELSE CONVERT(VARCHAR(MAX),0) END 
     -- If not Simultaneous, select the primary rate's Report Code. 
     ELSE CONVERT(VARCHAR(MAX),R.ReportCode) 
     END AS 'PremCode' 
    ,O.Col1 
    ,O.Col2 
    ,O.Col3 
FROM 
    Orders O 
    INNER JOIN OrderRateCalculation ORC 
     ON O.OrdersID = ORC.OrdersID 
    LEFT JOIN Rate R 
     ON ORC.RateID = R.RateID 
    LEFT JOIN RateEffectiveDate RED 
     ON R.RateID = RED.RateID 
    LEFT JOIN RateEngineSimultaneous RES 
     ON RED.RateEffectiveDateID = RES.RateEffectiveDateID 
WHERE 
    (ORC.LoanHolder = 1) 

Answer 1

这是困难的没有你的表的更多的知识来回答关系，但如果是我，我会尝试找到一种方法来移动连接到‘贷款’到主加入列表，并删除子选集。类似这样的：

SELECT 
CASE 
    WHEN ORC.SimultaneousCalculation = 1 
     THEN CASE 
       WHEN -- Select the Owner's Simultaneous Report Code when Sales Price >= Loan Amount. 
         CASE 
          WHEN (ISNULL(O.OwnerPolicyLiability, 0) = 0) 
           THEN O.SalesPrice 
          WHEN (ISNULL(O.OwnerPolicyLiability, 0) > 0) 
           THEN (O.OwnerPolicyLiability) 
          ELSE 0 END AS 'SalesPrice' 
         >= 
         CASE 
          WHEN (ISNULL(L.LoanPolicyLiability, 0) = 0) 
           THEN L.Amount 
          WHEN (ISNULL(L.LoanPolicyLiability, 0) > 0) 
           THEN (L.Amount) 
          ELSE 0 END AS 'LoanAmount' 
        THEN CONVERT(VARCHAR(MAX),RES.SalesPolicyCode) 
       WHEN -- Select the Owner's Simultaneous Report Code when Sales Price < Loan Amount. 
         CASE 
          WHEN (ISNULL(O.OwnerPolicyLiability, 0) = 0) 
           THEN O.SalesPrice 
          WHEN (ISNULL(O.OwnerPolicyLiability, 0) > 0) 
           THEN (O.OwnerPolicyLiability) 
          ELSE 0 END AS 'SalesPrice' 
         < 
         CASE 
          WHEN (ISNULL(L.LoanPolicyLiability, 0) = 0) 
           THEN L.Amount 
          WHEN (ISNULL(L.LoanPolicyLiability, 0) > 0) 
           THEN (L.Amount) 
          ELSE 0 END AS 'LoanAmount' 
        THEN CONVERT(VARCHAR(MAX),RES.LoanPolicyCode) 
       ELSE CONVERT(VARCHAR(MAX),0) END 
    -- If not Simultaneous, select the primary rate's Report Code. 
    ELSE CONVERT(VARCHAR(MAX),R.ReportCode) 
    END AS 'PremCode' 
,O.Col1 
,O.Col2 
,O.Col3 
FROM 
Orders O 
INNER JOIN OrderRateCalculation ORC 
    ON O.OrdersID = ORC.OrdersID 
LEFT JOIN Rate R 
    ON ORC.RateID = R.RateID 
LEFT JOIN RateEffectiveDate RED 
    ON R.RateID = RED.RateID 
LEFT JOIN RateEngineSimultaneous RES 
    ON RED.RateEffectiveDateID = RES.RateEffectiveDateID 
LEFT JOIN Loan L 
    ON O.OrdersID = L.OrdersID 
    AND L.LoanOrder = 1 
WHERE 
(ORC.LoanHolder = 1) 

祝你好运！