SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


That case filter


That case filter

Author
Message
Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28799 Visits: 9671
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?
venoym
venoym
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1461 Visits: 2082
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.
murray-906152
murray-906152
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2963 Visits: 287
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.
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7801 Visits: 25280
[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
Dhruvesh Shah
Dhruvesh Shah
SSChasing Mays
SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)SSChasing Mays (653 reputation)

Group: General Forum Members
Points: 653 Visits: 237
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
Deepak Sharma-2311
Deepak Sharma-2311
Right there with Babe
Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)Right there with Babe (762 reputation)

Group: General Forum Members
Points: 762 Visits: 462
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
palotaiarpad
palotaiarpad
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2088 Visits: 774
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?
Mighty
Mighty
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4410 Visits: 1697
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."
Carlo Romagnano
Carlo Romagnano
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4895 Visits: 3326
But if that is not the case then the answer should be "There is NO query returning rows."

I agree with you!

I run on tuttopodismo
andagr
andagr
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search