Server 2008 r2用例表达式不能正确计算

Question

我得到了以下SQL语句，它无法确定字段何时应该具有'Y‘值。这是SQL..。

SELECT A.BUSINESS_UNIT 
 , A.WO_ID 
 , A.WO_TASK_ID 
 , A.ENS_TSK_CRT_VAL 
 , (CASE WHEN A.ENS_TSK_CRT_V01 = 'Y' THEN B.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V01  
 , (CASE WHEN A.ENS_TSK_CRT_V02 = 'Y' THEN C.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V02 
 , (CASE WHEN A.ENS_TSK_CRT_V03 = 'Y' THEN D.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V03  
 , (CASE WHEN A.ENS_TSK_CRT_V04 = 'Y' THEN E.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V04 
 , (CASE WHEN A.ENS_TSK_CRT_V05 = 'Y' THEN F.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V05 
 , (CASE WHEN A.ENS_TSK_CRT_V06 = 'Y' THEN G.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V06 
 , (CASE WHEN A.ENS_TSK_CRT_V07 = 'Y' THEN H.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V07 
 , (CASE WHEN A.ENS_TSK_CRT_V08 = 'Y' THEN I.DESCR ELSE ' ' END) AS ENS_TSK_CRT_V08 
 FROM PS_ENS_WM_TSKCR_VW A LEFT OUTER JOIN ( 
   SELECT A.BUSINESS_UNIT 
   , A.WO_TASK_ID 
   , C.DESCR 
   , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '01' ) B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND B.WO_ID = A.WO_ID 
      AND B.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '02' ) C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND C.WO_ID = A.WO_ID 
      AND C.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '03' ) D ON D.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND D.WO_ID = A.WO_ID 
      AND D.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '04' ) E ON E.BUSINESS_UNIT = A.BUSINESS_UNIT 
             AND E.WO_ID = A.WO_ID 
      AND E.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '05' ) F ON F.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND F.WO_ID = A.WO_ID 
      AND F.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '06' ) G ON G.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND G.WO_ID = A.WO_ID 
      AND G.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '07' ) H ON H.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND H.WO_ID = A.WO_ID 
      AND H.WO_TASK_ID = A.WO_TASK_ID LEFT OUTER JOIN ( 
    SELECT A.BUSINESS_UNIT 
    , A.WO_TASK_ID 
    , C.DESCR 
    , A.WO_ID 
    , C.ENS_TSK_CRT_CD 
     FROM PS_ENS_WM_TSKCR_VW A 
     , PS_ENS_WM_TSK_CRT C 
    WHERE A.ENS_TSK_CRT_V01 = 'Y' 
      AND C.SETID = 'SHARE' 
      AND C.ENS_TSK_CRT_CD = '08' ) I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT 
      AND I.WO_ID = A.WO_ID 
      AND I.WO_TASK_ID = A.WO_TASK_ID  
    WHERE ( A.ENS_TSK_CRT_VAL <> 0 
    AND A.WO_ID = '0000002151'
    AND A.BUSINESS_UNIT = 'R3851' )

case语句有时取起'Y‘并按预期行事，有时它们没有。正在检查'Y’值的字段是长度为一个字符的CHAR字段。我尝试过转换和转换，以确保与'Y‘相比，这个值是我在case语句中所期望的。我试着将一个‘’>和'<‘连接到任何一方，看看是否有空白填充值，但事实并非如此.这看起来就像case语句完全停止了工作一样。这里有一个数据外观的小样本，为了让您了解它是如何工作的，with...sorry有点混乱。

WO_TASK_ID  WO_ID   ENS_TSK_CRT_VAL ENS_TSK_CRT_V01 ENS_TSK_CRT_V02 ENS_TSK_CRT_V03
1           0000002151     2    Y   N   N
9           0000002151   12 N   Y   Y
12          0000002151     52   N   Y   N

后来我用完全不同的方法解决了这个问题，但我只想知道这种情况是如何发生的，以及在将来再次使用CASE表达式时，应该如何克服这个问题。

任何帮助都是非常感谢的。

• 弗林

Answer 1

可能这对你有帮助-

;WITH cte AS 
(
    SELECT  A.BUSINESS_UNIT ,
            A.WO_TASK_ID ,
            C.DESCR ,
            A.WO_ID ,
            C.ENS_TSK_CRT_CD
    FROM PS_ENS_WM_TSKCR_VW A ,
         PS_ENS_WM_TSK_CRT C
    WHERE A.ENS_TSK_CRT_V01 = 'Y'
        AND C.SETID = 'SHARE'
        AND C.ENS_TSK_CRT_CD IN (
                    '01', '02', '03', '04', 
                    '05', '06', '07', '08'
                )
)
SELECT  A.BUSINESS_UNIT ,
        A.WO_ID ,
        A.WO_TASK_ID ,
        A.ENS_TSK_CRT_VAL ,
        ISNULL(B.DESCR, ' ') AS ENS_TSK_CRT_V01 ,
        ISNULL(C.DESCR, ' ') AS ENS_TSK_CRT_V02 ,
        ISNULL(D.DESCR, ' ') AS ENS_TSK_CRT_V03 ,
        ISNULL(E.DESCR, ' ') AS ENS_TSK_CRT_V04 ,
        ISNULL(F.DESCR, ' ') AS ENS_TSK_CRT_V05 ,
        ISNULL(G.DESCR, ' ') AS ENS_TSK_CRT_V06 ,
        ISNULL(H.DESCR, ' ') AS ENS_TSK_CRT_V07 ,
        ISNULL(I.DESCR, ' ') AS ENS_TSK_CRT_V08
FROM PS_ENS_WM_TSKCR_VW A
LEFT JOIN cte B ON B.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND B.WO_ID = A.WO_ID
    AND B.WO_TASK_ID = A.WO_TASK_ID
    AND B.ENS_TSK_CRT_CD = '01'
    AND A.ENS_TSK_CRT_V01 = 'Y'
LEFT JOIN cte C ON C.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND C.WO_ID = A.WO_ID
    AND C.WO_TASK_ID = A.WO_TASK_ID
    AND C.ENS_TSK_CRT_CD = '02'
    AND A.ENS_TSK_CRT_V02 = 'Y'
LEFT JOIN cte D ON D.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND D.WO_ID = A.WO_ID
    AND D.WO_TASK_ID = A.WO_TASK_ID
    AND D.ENS_TSK_CRT_CD = '03'
    AND A.ENS_TSK_CRT_V03 = 'Y'
LEFT JOIN cte E ON E.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND E.WO_ID = A.WO_ID
    AND E.WO_TASK_ID = A.WO_TASK_ID
    AND E.ENS_TSK_CRT_CD = '04'
    AND A.ENS_TSK_CRT_V04 = 'Y'
LEFT JOIN cte F ON F.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND F.WO_ID = A.WO_ID
    AND F.WO_TASK_ID = A.WO_TASK_ID
    AND F.ENS_TSK_CRT_CD = '05'
    AND A.ENS_TSK_CRT_V05 = 'Y'
LEFT JOIN cte G ON G.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND G.WO_ID = A.WO_ID
    AND G.WO_TASK_ID = A.WO_TASK_ID
    AND G.ENS_TSK_CRT_CD = '06'
    AND A.ENS_TSK_CRT_V06 = 'Y'
LEFT JOIN cte H ON H.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND H.WO_ID = A.WO_ID
    AND H.WO_TASK_ID = A.WO_TASK_ID
    AND H.ENS_TSK_CRT_CD = '07'
    AND A.ENS_TSK_CRT_V07 = 'Y'
LEFT JOIN cte I ON I.BUSINESS_UNIT = A.BUSINESS_UNIT
    AND I.WO_ID = A.WO_ID
    AND I.WO_TASK_ID = A.WO_TASK_ID
    AND I.ENS_TSK_CRT_CD = '08'
    AND A.ENS_TSK_CRT_V08 = 'Y'
WHERE A.ENS_TSK_CRT_VAL != 0
    AND A.WO_ID = '0000002151'
    AND A.BUSINESS_UNIT = 'R3851'

Answer 2

BTW，为了防止任何人对感到好奇，这是我在发布关于案件表达式的问题之前使用的解决方法.

SELECT A.BUSINESS_UNIT , A.WO_ID , A.WO_TASK_ID , A.ENS_TSK_CRT_VAL 
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,1) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 01 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL01
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,2) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 02 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL02
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,3) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 03 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL03
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,4) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 04 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL04
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,5) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 05 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL05
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,6) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 06 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL06
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,7) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 07 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL07
 , (CASE WHEN (CASE WHEN CAST(A.ENS_TSK_CRT_VAL AS INT) & POWER(2,8) > 0 THEN 1 ELSE 0 END) = 1 THEN (SELECT DESCR FROM PS_ENS_WM_TSK_CRT WHERE ENS_TSK_CRT_CD = 08 AND SETID = 'SHARE') ELSE ' ' END) AS ENS_TSK_CRT_VAL08
 FROM PS_ENS_WM_TSKCR_WO A 
WHERE A.ENS_TSK_CRT_VAL <> 0
AND A.WO_ID = '0000002151' AND A.BUSINESS_UNIT = 'R3851'

Answer 3

我怀疑这是案件陈述的行为。我刚刚发现了2008年R2与2005年的不同之处，在我的搜索中发现了您的问题。用sql server 2008年R2处理的情况与2005年处理的情况不同。注意2008文档备注中的语句：“在某些情况下，在CASE语句接收表达式的结果作为其输入之前，计算表达式是可能的。在计算这些表达式时可能出错。”

因此，在2008年，所有潜在的产出可能会被评估，即使不合适(输入没有评估为真)，这是发生在我的一些存储过程后，升级到2008年。

因此，在您的情况下，无论B.DESCR、C.DESCR、D.DESCR等是否等于'Y‘、B.DESCR、C.DESCR、D.DESCR等等，都会被计算，并且在当时可能为null，或者在某种程度上无法与'Y’进行比较，从而导致语句的这一部分失败(这似乎是无声的)。不确定，但闻起来和我遇到的问题一样。唯一让我对这个“答案”持谨慎态度的是，我的问题在where子句中，而您的问题在sql语句的select部分中。

不管怎样，也许有人会觉得这很有帮助。花了些功夫才弄明白这点。