Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ninja's_RGR'us » That case filter
Add to briefcase
201 posts, Page 1 of 21
12345»»»

That case filter Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Ninja's_RGR'us
Posted Tuesday, July 19, 2011 9:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 21,359, Visits: 9,541
Comments posted to this topic are about the item That Case Filter

Hey guys, here's the full story behind that question. And no there is no typo in it.


I was trying to debug something fast for someone on ssc.com when I ran the exact code posted in the QOTD. I swear it took me a good 5 minutes to realize the extra union all was there (copy / paste error).

By then I was wasting my time researching KB bugs about different set options.

Steve and I went back and forth with this question and the exact wording to use. The bottom line was that I wanted you to be in the same situation I was when I was facing this "issue". I spent the first 2-3 minutes trying to debug the case filter and then about the same time researching for bugs and behavior on set options.

Also the title was misleading so not to give the answer away (like I wasn't going to name this "Too many union alls???").


I went back and forth on wether or not to post the question because I knew some of you wouldn't like it but I think this is a valuable lesson (it was for me anyways).

When something just doesn't make sense you need to test the most basic assumptions.

It's also a good reminder to not rush through things and not work when you're too tired (which I was that afternoon).



This is also meant as a fun discussion starter. You can use this scenario to laugh at me if you want (God<Some supreme being> knows I did).


So tell us about the last time you did something dumb, stupid, funny like this with Sql Server?
Post #1144668
venoym
Posted Tuesday, July 19, 2011 9:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:51 AM
Points: 721, Visits: 1,204
Interesting question... Has nothing to do with a case filter at all. This is a gotcha question seeing if you can spot the extra UNION ALL... it's somewhat misleading.

I don't care about points personally... I just hate when you ask a question and then the answer is not the question itself.
Post #1144670
murray-906152
Posted Tuesday, July 19, 2011 9:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, March 28, 2013 12:06 AM
Points: 1,527, Visits: 207
I think that this question is ambiguous and should be withdrawn.

There is NO query returning zero rows in the question.

There is one insert statement yielding five rows affected.

You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.

Regards, Murray.
Post #1144671
bitbucket-25253
Posted Tuesday, July 19, 2011 9:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 12:39 PM
Points: 5,103, Visits: 20,220
[Rant]
After getting the supposed incorrect answer, I cut and pasted the code into SQL 2005 and then again into 2008. Ran each not less than 15 times and ALWAYS got 5 rows ...

Can anyone explain WHY or REASON for my results?

I do respect Ninja's_RGR'us knowledge and experience, but ah well
[/Rant]

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read
Post #1144676
Dhruvesh Shah
Posted Tuesday, July 19, 2011 10:55 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, July 25, 2012 9:04 PM
Points: 542, Visits: 187
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
Post #1144694
Deepak Sharma-2311
Posted Tuesday, July 19, 2011 11:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:52 AM
Points: 582, Visits: 305
Hi bitbucket-25253,

The question says "How many rows returned by executing all of the following?" and there is only insert statement,
the rows would be returned only when we select from @tbl.
If you write SELECT * FROm @tbl at the end then it will return you the 5 rows.
Please correct me if I am wrong.

Deepak Kumar Sharma
Post #1144721
palotaiarpad
Posted Wednesday, July 20, 2011 12:29 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 12:06 AM
Points: 849, Visits: 323
I got the right answer, but please stop questions about CASE having really nothing to do with CASE itself! Or is QOD about fooling us around?
Post #1144736
Mighty
Posted Wednesday, July 20, 2011 12:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:33 AM
Points: 2,723, Visits: 987
murray-906152 (7/19/2011)
I think that this question is ambiguous and should be withdrawn.

There is NO query returning zero rows in the question.

There is one insert statement yielding five rows affected.

You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.

Regards, Murray.

I fully agree with this statement.

I didn't miss the fact that UNION ALL was before the select, so thought that the question was about the 5 rows that were affected, instead of returned.

But if that is not the case then the answer should be "There is NO query returning rows."
Post #1144741
Carlo Romagnano
Posted Wednesday, July 20, 2011 1:33 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:56 AM
Points: 1,972, Visits: 1,822
But if that is not the case then the answer should be "There is NO query returning rows."

I agree with you!
Post #1144757
andagr
Posted Wednesday, July 20, 2011 2:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 1:15 AM
Points: 191, Visits: 224
murray-906152 (7/19/2011)
I think that this question is ambiguous and should be withdrawn.

There is NO query returning zero rows in the question.

There is one insert statement yielding five rows affected.

You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.

Regards, Murray.


QFT

/@devandreas
Post #1144767
« Prev Topic | Next Topic »

Add to briefcase
201 posts, Page 1 of 21
12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.