Is my case statement broken by the use of outer apply?

1 vote

1 answer

1817 views

sql-server-2008 query-refactor case

                          I'm struggling with a case statement not working. Simple enough I need to supply a value of 0 where the question marks are. I have several other case statements which handle separate columns, but this is the simplest. I have used case statements with NULLS before and no issue. My best guess is the OUTER APPLY is screwing with things, but I can't find any documentation to back that up. 

    SELECT TOP 20
      GIVE_BACK1.CLUB_TOT 'B1_TOT',
      GC.CLUB_USER_VARBL3
    FROM
      SUMMIT.CLUB_MBRSP GC
        OUTER APPLY
        (
          SELECT
            CASE 
              WHEN B1.CLUB_USER_VARBL3 IS NULL
                THEN '?????????????????????????????'
              ELSE
                B1.CLUB_TOT
            END AS 'CLUB_TOT'
          FROM
            SUMMIT.CLUB_MBRSP B1
          WHERE
            GC.ID = B1.ID
            AND
            (
              (
                GC.CLUB_USER_VARBL3 != 'LT'
                AND B1.CLUB_USER_VARBL3 = LEFT(GC.CLUB_USER_VARBL3,2) + CONVERT(varchar(2),(CONVERT(INT,RIGHT(GC.CLUB_USER_VARBL3,3))-1))
              )
            )
        ) GIVE_BACK1

This query yields this result set

    NULL         FY88 
    NULL         FY10 
    NULL         FY01 
    NULL         FY99 
    NULL         FY97 
    NULL         FY99 
    NULL         FY97 
    NULL         FY02 
    NULL         FY01 
    17525.00     FY85 
    NULL         FY84
    ...

Any help getting the case statement working, and explanation of the issue or a refactoring would be most appreciated. 
                        

Asked by BZN_DBer (190 rep)

Dec 7, 2011, 12:34 AM
Last activity: Dec 7, 2011, 12:48 AM

Is my case statement broken by the use of outer apply?

Related Questions