case statement puzzle

Question

case statement puzzle

amit_adarsh

Ten Centuries

Points: 1273
More actions
May 25, 2010 at 9:35 pm

#223031

Comments posted to this topic are about the item case statement puzzle

Viewing 15 posts - 1 through 15 (of 16 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Nice question. I didn't know that constants (like 1) could be placed after CASE, so I definately learned something today.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sharath.chalamgari SSCertifiable Points: 5680 More actions · Answer 2

Nice Question,:-)

as you said once the condition is satisfied it will not look at other statement.

Andrew Watson-478275 SSCarpal Tunnel Points: 4652 More actions · Answer 3

I really need to stop being so mistrusting. The right answer was so obvious, I assumed it had to be a trick question and got it wrong :pinch:.

DugyC Hall of Fame Points: 3804 More actions · Answer 4

Good question that in its simplicity makes you question what seems the obvious answer and think about it more deeply.

According to SQL Books Online, the parser actually checks all conditions and returns the first true condition, if none then returns "else" if its coded otherwise NULL. That's how it seems to be described.

_____________________________________________________________________
[font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

Ujar Say Hey Kid Points: 669 More actions · Answer 5

Really a nice question..

I have got the confusion that, we need to declare the variable name followed by case statement and after that the value followed by when.

Thanks..

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 6

This one reminds me of the fundamental concepts I learned in C. 0=false, 1=true.

ken.trock SSCertifiable Points: 5147 More actions · Answer 7

da-zero (5/26/2010)
Nice question. I didn't know that constants (like 1) could be placed after CASE, so I definately learned something today.

Yeah, same here. Good thing a 4th choice like "error" wasn't supplied with the question or I might have gotten it wrong :exclamation:

brdudley SSCrazy Points: 2320 More actions · Answer 8

Steve Jones - Editor (5/26/2010)
This one reminds me of the fundamental concepts I learned in C. 0=false, 1=true.

If you are saying that it chooses the first one because 1 = true, that is not correct. It is using the Case syntax similar to a Switch statement. The Select is equivalent to

Select Case

When @flg = 1 then 'True'

When @flg = 1 then 'False'

Else 'No Flag'

Try changing the constant or the value of @flg. It will result in "No Flag"

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

Interesting. I did not know that a case could be written this way as well.

Thanks.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

sknox SSChampion Points: 12388 More actions · Answer 10

F1Droid (5/26/2010)
Good question that in its simplicity makes you question what seems the obvious answer and think about it more deeply.
According to SQL Books Online, the parser actually checks all conditions and returns the first true condition, if none then returns "else" if its coded otherwise NULL. That's how it seems to be described.

Yeah, I had the right answer in 5 seconds, but took another 3 minutes to answer the question because I was sure it couldn't be that simple...:w00t:

As for the Books Online reference, anyone know of a good way to introduce a condition with side-effects so we could verify if it does indeed check all conditions?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 11

I was thinking it has to be true, but wait....... So I spent a few more minutes thinking, only to come to the same conclusion.

schultkl SSC Enthusiast Points: 116 More actions · Answer 12

The BOL seems to imply the 1 in "CASE 1" might be interpreted as a bit constant. Is this true? It seems 0/1 values are bit constants.

Note: It would not change the answer in this case due to the implicit conversion, but like others I was mistrusting and looking at it from all different angles. Just curious from an understanding standpoint.

rajputrahulg Newbie Points: 7 More actions · Answer 13

Thanks for explaining the example clearly. I answered it correctly but through some different logic. after your explaination, I understand my mistake

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 14

The question was pretty simple.....! But I really liked it.

Since the @flg is defined with value = 1 and it is matching 1 again in Case statement so result should be obviously 'true'.

Nice question...keep posting more question.... 🙂

{{{{

Declare @flg as int

SEt @flg = 1

select case 1 when @flg then 'true'

when @flg then 'false'

else 'noflg'

}}}}

end as flag

Thanks.