Home Forums SQL Server 2008 T-SQL (SS2K8) How To Use IS NOT NULL in a Case Statement RE: How To Use IS NOT NULL in a Case Statement

  • August 2, 2013 at 1:32 am

    #1638073

    Teee (8/2/2013)

    Lol! it actually worked when I used IN instead of = but the data that was returned wasn't correct, the second bit works fine.

    Thanks

    That's because the subquery in Kapil's code

    and it.bitIsAsset = Case @bitAsset When 1 Then (SELECT it.intItemId FROM dtlItem it WHERE it.intItemId IS NOT NULL) Else 'All' End

    isn't correlated to the outer SELECT - it can return any old rows so long as intItemId IS NOT NULL.

    Note that the LEFT JOIN to dtlItem will be converted to an INNER JOIN by referencing columns in the WHERE clause (unless you are checking for a NULL).

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden