MS SQL视图CASE WHEN报错：预期布尔表达式却传入非布尔类型

问题描述 #

尝试给现有SQL视图新增如下代码片段时触发错误：

(CASE WHEN NLNominalAccount.DebitYearToDate - NLNominalAccount.CreditYearToDate = 0 THEN 0 ELSE 1 END) AS IncludeMe

错误提示：

在需要条件的上下文中指定了非布尔类型的表达式。

移除该片段后视图正常运行，尝试给判断条件单独加括号仍无法解决问题。

完整视图代码 #

CREATE VIEW [dbo].[VwCenNLAcccountPeriodMatrix]
AS
SELECT        dbo.NLNominalAccount.NLNominalAccountID, dbo.NLNominalAccount.AccountName, dbo.NLNominalAccount.AccountNumber, dbo.NLNominalAccount.AccountCostCentre, CASE WHEN P1Values.ActualVal IS NULL 
                         THEN 0 ELSE P1Values.ActualVal END AS P1Act, CASE WHEN P1Values.BudgetVal IS NULL THEN 0 ELSE P1Values.BudgetVal END AS P1Budget, CASE WHEN P2Values.ActualVal IS NULL 
                         THEN 0 ELSE P2Values.ActualVal END AS P2Act, CASE WHEN P2Values.BudgetVal IS NULL THEN 0 ELSE P2Values.BudgetVal END AS P2Budget, CASE WHEN P3Values.ActualVal IS NULL 
                         THEN 0 ELSE P3Values.ActualVal END AS P3Act, CASE WHEN P3Values.BudgetVal IS NULL THEN 0 ELSE P3Values.BudgetVal END AS P3Budget, CASE WHEN P4Values.ActualVal IS NULL 
                         THEN 0 ELSE P4Values.ActualVal END AS P4Act, CASE WHEN P4Values.BudgetVal IS NULL THEN 0 ELSE P4Values.BudgetVal END AS P4Budget, CASE WHEN P5Values.ActualVal IS NULL 
                         THEN 0 ELSE P5Values.ActualVal END AS P5Act, CASE WHEN P5Values.BudgetVal IS NULL THEN 0 ELSE P5Values.BudgetVal END AS P5Budget, CASE WHEN P6Values.ActualVal IS NULL 
                         THEN 0 ELSE P6Values.ActualVal END AS P6Act, CASE WHEN P6Values.BudgetVal IS NULL THEN 0 ELSE P6Values.BudgetVal END AS P6Budget, CASE WHEN P7Values.ActualVal IS NULL 
                         THEN 0 ELSE P7Values.ActualVal END AS P7Act, CASE WHEN P7Values.BudgetVal IS NULL THEN 0 ELSE P7Values.BudgetVal END AS P7Budget, CASE WHEN P8Values.ActualVal IS NULL 
                         THEN 0 ELSE P8Values.ActualVal END AS P8Act, CASE WHEN P8Values.BudgetVal IS NULL THEN 0 ELSE P8Values.BudgetVal END AS P8Budget, CASE WHEN P9Values.ActualVal IS NULL 
                         THEN 0 ELSE P9Values.ActualVal END AS P9Act, CASE WHEN P9Values.BudgetVal IS NULL THEN 0 ELSE P9Values.BudgetVal END AS P9Budget, CASE WHEN P10Values.ActualVal IS NULL 
                         THEN 0 ELSE P10Values.ActualVal END AS P10Act, CASE WHEN P10Values.BudgetVal IS NULL THEN 0 ELSE P10Values.BudgetVal END AS P10Budget, CASE WHEN P11Values.ActualVal IS NULL 
                         THEN 0 ELSE P11Values.ActualVal END AS P11Act, CASE WHEN P11Values.BudgetVal IS NULL THEN 0 ELSE P11Values.BudgetVal END AS P11Budget, CASE WHEN P12Values.ActualVal IS NULL 
                         THEN 0 ELSE P12Values.ActualVal END AS P12Act, CASE WHEN P12Values.BudgetVal IS NULL THEN 0 ELSE P12Values.BudgetVal END AS P12Budget, dbo.NLNominalAccount.AccountDepartment, 
                         dbo.NLAccountReportCategory.Code AS RepCatCode, dbo.NLAccountReportCategory.Description AS RepCatDesc, dbo.NLAccountReportType.Name AS RepType, 
                         dbo.NLNominalAccount.AccountDepartment + '/' + dbo.NLAccountReportCategory.Code AS DepCode, dbo.NLNominalAccount.AccountCostCentre + '/' + dbo.NLAccountReportCategory.Code AS CCCode, 
                         CASE WHEN ((CASE WHEN P1Values.ActualVal IS NULL THEN 0 ELSE P1Values.ActualVal END) = 0 AND (CASE WHEN P1Values.BudgetVal IS NULL THEN 0 ELSE P1Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P2Values.ActualVal IS NULL THEN 0 ELSE P2Values.ActualVal END) = 0 AND (CASE WHEN P2Values.BudgetVal IS NULL THEN 0 ELSE P2Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P3Values.ActualVal IS NULL THEN 0 ELSE P3Values.ActualVal END) = 0 AND (CASE WHEN P3Values.BudgetVal IS NULL THEN 0 ELSE P3Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P4Values.ActualVal IS NULL THEN 0 ELSE P4Values.ActualVal END) = 0 AND (CASE WHEN P4Values.BudgetVal IS NULL THEN 0 ELSE P4Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P5Values.ActualVal IS NULL THEN 0 ELSE P5Values.ActualVal END) = 0 AND (CASE WHEN P5Values.BudgetVal IS NULL THEN 0 ELSE P5Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P6Values.ActualVal IS NULL THEN 0 ELSE P6Values.ActualVal END) = 0 AND (CASE WHEN P6Values.BudgetVal IS NULL THEN 0 ELSE P6Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P7Values.ActualVal IS NULL THEN 0 ELSE P7Values.ActualVal END) = 0 AND (CASE WHEN P7Values.BudgetVal IS NULL THEN 0 ELSE P7Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P8Values.ActualVal IS NULL THEN 0 ELSE P8Values.ActualVal END) = 0 AND (CASE WHEN P8Values.BudgetVal IS NULL THEN 0 ELSE P8Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P9Values.ActualVal IS NULL THEN 0 ELSE P9Values.ActualVal END) = 0 AND (CASE WHEN P9Values.BudgetVal IS NULL THEN 0 ELSE P9Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P10Values.ActualVal IS NULL THEN 0 ELSE P10Values.ActualVal END) = 0 AND (CASE WHEN P10Values.BudgetVal IS NULL THEN 0 ELSE P10Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P11Values.ActualVal IS NULL THEN 0 ELSE P11Values.ActualVal END) = 0 AND (CASE WHEN P11Values.BudgetVal IS NULL THEN 0 ELSE P11Values.BudgetVal END) = 0 AND 
                         (CASE WHEN P12Values.ActualVal IS NULL THEN 0 ELSE P12Values.ActualVal END) = 0 AND (CASE WHEN P12Values.BudgetVal IS NULL THEN 0 ELSE P12Values.BudgetVal END) = 0 AND
                        (CASE WHEN NLNominalAccount.DebitYearToDate - NLNominalAccount.CreditYearToDate = 0 THEN 0 ELSE 1 END) AS IncludeMe, dbo.NLNominalAccount.AccountCostCentre + '/' + dbo.NLNominalAccount.AccountDepartment + '/' + dbo.NLAccountReportCategory.Code AS CCDepCode
FROM            dbo.NLAccountReportCategory INNER JOIN
                         dbo.NLNominalAccount ON dbo.NLAccountReportCategory.NLAccountReportCategoryID = dbo.NLNominalAccount.NLAccountReportCategoryID INNER JOIN
                         dbo.NLAccountReportType ON dbo.NLAccountReportCategory.NLAccountReportTypeID = dbo.NLAccountReportType.NLAccountReportTypeID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_2
                               WHERE        (PeriodNumber = 2)) AS P2Values ON dbo.NLNominalAccount.NLNominalAccountID = P2Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_1
                               WHERE        (PeriodNumber = 1)) AS P1Values ON dbo.NLNominalAccount.NLNominalAccountID = P1Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_3
                               WHERE        (PeriodNumber = 3)) AS P3Values ON dbo.NLNominalAccount.NLNominalAccountID = P3Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_4
                               WHERE        (PeriodNumber = 4)) AS P4Values ON dbo.NLNominalAccount.NLNominalAccountID = P4Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_5
                               WHERE        (PeriodNumber = 5)) AS P5Values ON dbo.NLNominalAccount.NLNominalAccountID = P5Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_6
                               WHERE        (PeriodNumber = 6)) AS P6Values ON dbo.NLNominalAccount.NLNominalAccountID = P6Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_7
                               WHERE        (PeriodNumber = 7)) AS P7Values ON dbo.NLNominalAccount.NLNominalAccountID = P7Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_8
                               WHERE        (PeriodNumber = 8)) AS P8Values ON dbo.NLNominalAccount.NLNominalAccountID = P8Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_9
                               WHERE        (PeriodNumber = 9)) AS P9Values ON dbo.NLNominalAccount.NLNominalAccountID = P9Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_10
                               WHERE        (PeriodNumber = 10)) AS P10Values ON dbo.NLNominalAccount.NLNominalAccountID = P10Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_11
                               WHERE        (PeriodNumber = 11)) AS P11Values ON dbo.NLNominalAccount.NLNominalAccountID = P11Values.NLNominalAccountID LEFT OUTER JOIN
                             (SELECT        NLNominalAccountID, CONVERT(decimal(18, 5), ActualValue) + CONVERT(decimal(18, 5), AdjustmentAfterYearEndClose) AS ActualVal, PeriodNumber, CONVERT(decimal(18, 5), BudgetValue) 
                                                         AS BudgetVal
                               FROM            dbo.VwCenNLAccountPeriodValue AS VwCenNLAccountPeriodValue_12
                               WHERE        (PeriodNumber = 12)) AS P12Values ON dbo.NLNominalAccount.NLNominalAccountID = P12Values.NLNominalAccountID

GO

问题根源 #

你把新增的IncludeMe列错误地嵌套进了前面未闭合的大CASE WHEN的条件中：

1. 前面的长CASE WHEN只写了WHEN和一堆布尔条件，没有用END闭合，也没有指定列别名，SQL会认为这个CASE表达式还在编写中。
2. 你用AND连接了大CASE的最后一个条件和新增的IncludeMe的CASE表达式，SQL会期望AND后面是一个布尔条件，但你的CASE表达式返回的是数值（0或1），不是布尔值，因此触发错误。

修正方案 #

1. 闭合前面的大CASE WHEN，给它指定一个合理的列别名（比如AllPeriodsZeroFlag）。
2. 移除连接大CASE和IncludeMe的多余AND，让IncludeMe作为独立的SELECT列存在。

修正后的关键代码片段：

CASE WHEN ((CASE WHEN P1Values.ActualVal IS NULL THEN 0 ELSE P1Values.ActualVal END) = 0 AND (CASE WHEN P1Values.BudgetVal IS NULL THEN 0 ELSE P1Values.BudgetVal END) = 0 AND 
(CASE WHEN P2Values.ActualVal IS NULL THEN 0 ELSE P2Values.ActualVal END) = 0 AND (CASE WHEN P2Values.BudgetVal IS NULL THEN 0 ELSE P2Values.BudgetVal END) = 0 AND 
-- 省略中间相同的条件
(CASE WHEN P12Values.ActualVal IS NULL THEN 0 ELSE P12Values.ActualVal END) = 0 AND (CASE WHEN P12Values.BudgetVal IS NULL THEN 0 ELSE P12Values.BudgetVal END) = 0) 
END AS AllPeriodsZeroFlag, -- 闭合大CASE并添加别名
(CASE WHEN NLNominalAccount.DebitYearToDate - NLNominalAccount.CreditYearToDate = 0 THEN 0 ELSE 1 END) AS IncludeMe,

这样修正后，视图的语法完全符合SQL规范，新增的IncludeMe列可以正常工作。

内容的提问来源于stack exchange，提问作者Toasty859