Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

The Outer Apply is working like a Left Outer Join so when no row is returned by the Outer Apply a row filled with NULLs is returned. I would question the need for the Outer Apply but placing a Coalesce(GIVE_BACK1.CLUB_TOT,0) in the SELECT list would fix the immediate issue.

share|improve this answer
    
Your right, the cross apply doesn't make much sense. Thanks for the answer and the critique! –  BZN_DBer Dec 7 '11 at 16:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.