Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

How To Use IS NOT NULL in a Case Statement Expand / Collapse
Author
Message
Posted Friday, August 2, 2013 1:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1480281
Posted Friday, August 2, 2013 1:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,891, Visits: 2,329


Thanks Chris for correcting........

Teee: I apologize you for not providing the proper solution!!



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1480292
Posted Friday, August 2, 2013 1:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,891, Visits: 2,329
Actually I didnt see the outer query and was just focused on correcting Case statement...
I read query and see that case can return multiple values and this can lead to incorrect result.........



_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1480293
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse