That case filter

Question

Post reply

That case filter

Viewing 15 posts - 46 through 60 (of 200 total)

You must be logged in to reply to this topic. Login to reply

paul s-306273 SSChampion Points: 10730 More actions · Answer 1

OK, was the point of the question

1 - to make us aware that we need to read things properly

2 - to make the majority or readers look stupid

or

3 - to make the question setter look superior?

calvo SSChampion Points: 12930 More actions · Answer 2

I noticed the extra UNION ALL and assumed the author got carried away with copy/paste, so I deleted the extra UNION ALL. Doing so will "return" 3 rows, hence my answer.

Originally this query doesn't "return" a dataset, it "affects" 5 rows in the temp table but never "returns" them. To me, "0 rows returned" means something like this

use master

go

select * from sys.objects

where object_id = 0

Where you are given a dataset with 0 rows.

meh... if the question had a title that identified your intent, it might have been more clear. With a title of "That case filter", one can only assume you're trying to teach us how nested case statements work in a where clause, not how to debug syntax in the previous insert statement.

Thanks for the effort though.

______________________________________________________________________________________________
Forum posting etiquette.[/url] Get your answers faster.

jcunningham Old Hand Points: 397 More actions · Answer 3

For those of us who saw the extra union all and took it out, is the correct answer 3 then (0,1,2)? I ran it like everyone else and thought, that can't be right. Then I thought, I wonder if they are doing this on purpose. Had I said yes, I would have went with 0, darn!

One thing that had me for the longest time that I later laughed about was the following....

I had a developer telling me that his Where column like @variable (variable = 'a%') was not returning all of the expected results. Took me forever to realize he was running it on a variable of type char(10) instead of varchar(10). I agree with you that sometimes these silly typos or not having all of the information can lead to some wasted time. Just have to laugh at yourself in the end. No sense in getting worked up about it.

Cheers

hakim.ali SSCarpal Tunnel Points: 4239 More actions · Answer 4

I consider most people on this forum SQL Gods and SQL Gurus (don't ask me who's who!), but in this case I have to say the question was improperly worded and the answer provided is actually incorrect. The result should be 5, because 5 rows are inserted into the table variable.

Hakim Ali
www.sqlzen.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Thomas Abraham (7/20/2011)
Ninja's_RGR'us (7/20/2011)
You're welcomed. So when's the last time you faced a similar issue like I did?
I make mistakes all the time. People that type like I do seldom go into computing! (That's one reason I spent years in data modeling - less typing! 😉 )
Most of my mistakes get caught by SMS. Sadly, while I'm sure there's a good one that happened in the not too distant past, I'm not able to recall any particular similar incident. Blame it on old age.

Cat got your tongue?? 😀

BenWard SSCertifiable Points: 5903 More actions · Answer 6

jcunningham (7/20/2011)
I had a developer telling me that his Where column like 'A%' was not returning all of the expected results. Took me forever to realize he was running it on a char(10) column instead of varchar(10). I agree with you that sometimes these silly typos or not having all of the information can lead to some wasted time. Just have to laugh at yourself in the end. No sense in getting worked up about it.

What has char/varchar got to do with it?

declare @Foo as char(10)

set @Foo = 'BoopyBoppy'

if @Foo like 'B%'

select 'yay'

returns yay...

why would char/varchar affect a like clause?

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

paul s-306273 (7/20/2011)
OK, was the point of the question
1 - to make us aware that we need to read things properly
2 - to make the majority or readers look stupid
or
3 - to make the question setter look superior?

1 - Well you already know that already.

2 - No way

3 - No even close.

Actually if you have time to read all my message it's one of those times where I felt the stupidest and I wanted to share the experience. So it's actually quite the opposite of #3.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

hakimali (7/20/2011)
I consider most people on this forum SQL Gods and SQL Gurus (don't ask me who's who!), but in this case I have to say the question was improperly worded and the answer provided is actually incorrect. The result should be 5, because 5 rows are inserted into the table variable.

The question says how many rows RETURNED. Please read my first post on top.

Tx.

bopeavy SSCertifiable Points: 5869 More actions · Answer 9

I agree the question was misleading!! I ran it 20 times and always 5 rows affected! If the question was in reference to the UNION ALL it should have been worded in away people would have been Looking for a different answer.

BenWard
//edit - don't forget, if this question - or it's answer - was actually wrong it wouldn't have been published.

I think that sometimes this can be a matter of perception when you do not get the whole story.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

calvo (7/20/2011)
I noticed the extra UNION ALL and assumed the author got carried away with copy/paste.

Exactly what happened when I first created the script... hence the start of the rest of my problems.

calvo (7/20/2011)
Originally this query doesn't "return" a dataset, it "affects" 5 rows in the temp table but never "returns" them. To me, "0 rows returned" means something like this
use master
go
select * from sys.objects
where object_id = 0
Where you are given a dataset with 0 rows.
meh... if the question had a title that identified your intent, it might have been more clear. With a title of "That case filter", one can only assume you're trying to teach us how nested case statements work in a where clause, not how to debug syntax in the previous insert statement.
Thanks for the effort though.

Please [re]read my edit in the first post.

Also I'd love to know how you would have phrased that question without giving the answer away. Steve and I tried our best but we couldn't find any better wording.

BenWard SSCertifiable Points: 5903 More actions · Answer 11

bopeavy (7/20/2011)
I agree the question was misleading!! I ran it 20 times and always returned 5 rows! If the question was in reference to the UNION ALL it should have been worded in away people would have been Looking for a different answer.

it affected5 rows.

If it ever returned 5 rows you must have changed the query.

No Results grid means nothing returned 🙂

Ben

^ Thats me!

----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

jcunningham (7/20/2011)
I had a developer telling me that his Where column like 'A%' was not returning all of the expected results. Took me forever to realize he was running it on a char(10) column instead of varchar(10). I agree with you that sometimes these silly typos or not having all of the information can lead to some wasted time. Just have to laugh at yourself in the end. No sense in getting worked up about it.
Cheers

Thanks for sharing. Believe me I had a good laugh and that's what made me post the question (anyone can use a good laugh once in a while).

Edit : I think you got the wrong gotcha, or the wrong explaination of it. Char or charchar makes not difference in that case. Case sensibility maybe but not char.

OCTom SSChampion Points: 11785 More actions · Answer 13

😛

It's good to know that even those will guru-like SQL Server knowledge make these types of mistakes. 😛

This has happened to me (not this EXACT scenario) before. After looking at code for a period of time, taking a break, and coming back, the problem seems to jump out at me. Another method to get unstuck is to have someone else look at the code. I even have people who know nothing about SQL or development look at code and their perspective is sometimes valuable.

Thanks for the question and the humorous (at least to me) story behind it. All I can say is "been there; done that."

bopeavy SSCertifiable Points: 5869 More actions · Answer 14

BenWard (7/20/2011)
bopeavy (7/20/2011)
--------------------------------------------------------------------------------
I agree the question was misleading!! I ran it 20 times and always returned 5 rows! If the question was in reference to the UNION ALL it should have been worded in away people would have been Looking for a different answer.
it affected5 rows.
If it ever returned 5 rows you must have changed the query.
No Results grid means nothing returned
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010

Nicely said, but as I said before this could be a matter of perception.

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

Toreador SSChampion Points: 11367 More actions · Answer 15

BenWard (7/20/2011)
it affected 5 rows.

or perhaps

it returned "5 rows"

😉