SQL Server 2008 r2 CASE Expression not evaluating correctly

Question

I've got the following SQL statement that is not determining when a field has a value of 'Y' when it should. Here is the 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' )

The case statements sometimes pick up the 'Y' and behave as expected, sometimes they don't. The fields being examined for 'Y' values are CHAR fields of one character in length. I've tried casting and converting to make certain that the value was what I was expecting in the case statement when compared to 'Y'. I've tried concatenating a '>' and '<' to either side to see if somehow there were whitespaces padding the value, and that was not the case at all. It just appears as though the case statement simply ceased working. Here is a small sample of what the data looked like, to give you an idea of what it was working with...sorry it's a little bit scrambled.

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

I've since fixed the problem by taking a completely different route, but I just want to know how this is happening, and what to do to overcome the problem in the future when working with the CASE Expression again.

Any help is greatly appreciated...

• Flynn

Answer 1

I suspect it is the behavior of the case statement. I just discovered a difference in 2008 R2 vs 2005 and in my searches came upon your problem. Case is handled by sql server 2008 R2 differently than 2005. Note the statement in the remarks of the 2008 doc: "In some situations, an expression is evaluated before a CASE statement receives the results of the expression as its input. Errors in evaluating these expressions are possible."

So in 2008 all potential outputs may be evaluated even if not appropriate (the input doesn't evaluate to true), which is occuring in some stored procs of mine now after upgrading to 2008.

So perhaps in your case, regardless of whether B.DESCR, C.DESCR, D.DESCR, etc. equal 'Y', B.DESCR, C.DESCR, D.DESCR, etc. get evaluated and may at that time be null or otherwise somehow unable to be compared to 'Y' thereby causing that part of the statement to fail (and this is silent it seems). Not sure but it smells the same as what I had for a problem. The only thing that makes me cautious about this "answer" is that my problem was within the where clause and yours is in the select part of the sql statement.

Regardless, maybe someone will find this helpful. It took some digging to figure this out.

Answer 2

BTW, just in case anyone is curious, here was the workaround I used before posting my question about the CASE Expression to the original problem......

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

Possible this be helpful for you -

;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 4

you can try to remove trailing space from Y which can cause similar issues