Assigning categories to values 2

Question

Post reply

Assigning categories to values 2

Viewing 15 posts - 16 through 30 (of 33 total)

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

Dave62 SSCertifiable Points: 7459 More actions · Answer 1

Nice follow up Hugo. I didn't over-think it today. 😉

A minor typo in the explanation.

"... As soon as one evaluates to True, the corresponding ELSE is returned. ..."

ELSE should be THEN in that sentence.

Enjoy!

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

hakim.ali

SSCarpal Tunnel

Points: 4239

May 15, 2013 at 8:39 am

#1615454

Good question, thanks.

Hakim Ali
www.sqlzen.com

Ken SSCoach Points: 17191 More actions · Answer 3

Nice question Hugo. I use a lot of case statements so these are always fun to navigate.

Hany Helmy SSChampion Points: 13921 More actions · Answer 4

In the Explanation: "A CASE expression will always evaluate the WHEN clauses from top to bottom. As soon as one evaluates to True, the corresponding [ELSE] is returned."

I think it should be the corresponding [THEN] not else, correct me if I am wrong.

And many thanks to SSC editors who put those 2 nice questions back to back to get the most benefit out of it.

Regards,

Hany Helmy

Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant

paul s-306273 SSChampion Points: 10727 More actions · Answer 5

I too lokked for the catch, didn't find one so submitted my answer.

Thanks for the 2 points Hugo.

(Well, some question setters do try to catch us out don't they?)

Hugo Kornelis SSC Guru Points: 64780 More actions · Answer 6

Dave62 (5/15/2013)
A minor typo in the explanation.
"... As soon as one evaluates to True, the corresponding ELSE is returned. ..."
ELSE should be THEN in that sentence.

and

hany.helmy (5/15/2013)
In the Explanation: "A CASE expression will always evaluate the WHEN clauses from top to bottom. As soon as one evaluates to True, the corresponding [ELSE] is returned."
I think it should be the corresponding [THEN] not else, correct me if I am wrong.

You are both absolutely right. Sorry for that mistake. Unfortunately, I am unable to change the explanation. 🙁

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Revenant SSC-Forever Points: 42467 More actions · Answer 7

It was so simple that I suspected a trick. 🙂

Thanks, Hugo!

johnf_amic Mr or Mrs. 500 Points: 532 More actions · Answer 8

I suspected a question like this would be coming soon, so I practiced yesterday! 😎

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 9

I was also looking for the trick:)

Regarding the aggregates & case evaluation:

the CASE evaluation order is guaranteed for the SELECT phase. I think it helps to understand it when one thinks about the aggregation as a type of "subquery".

Example: The following query throws an error because the aggregate "MIN(1/0)" cannot be calculated.

DECLARE @i int = 1;

SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1/0) END Agg

FROM sys.objects

This query is logically the same as the following query:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE AggSub END Agg

FROM (SELECT MIN(1/0) AggSub

FROM sys.objects

) S

I think that in this version, one can easier see when the error actually happens --> before the case evaluation starts. In the first version, the location of the aggregate function can mislead people to assume that this aggregate is only calculated during the evaluation of the CASE statement. But it is not. It is already performed before the CASE evaluation starts.

And to provide an example with aggregates that shows that the evaluation order is honored, consider this one:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE MIN(1) / 0 END Agg

FROM sys.objects

In this example, there is no error in the aggregation phase, as MIN(1) produces no error.

And there is no error in the SELECT phase, because the CASE evaluation stops right after the first WHEN (which is before the ELSE part which would cause an error).

And for completeness, here is the version in Subquery format:

DECLARE @i int = 1

SELECT CASE WHEN @i = 1 THEN 1 ELSE AggSub / 0 END Agg

FROM (SELECT MIN(1) AggSub

FROM sys.objects

) S

Best Regards,

Chris Büttner

Narud SSCrazy Points: 2837 More actions · Answer 10

Very positive knowledge acquired.

Thanks Hugo!

Ford Fairlane SSCertifiable Points: 7664 More actions · Answer 11

Good hump day QotD thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective

Anipaul SSC-Insane Points: 24681 More actions · Answer 12

Anipaul

SSC-Insane

Points: 24681

May 16, 2013 at 2:51 am

#1615652

Thanks Hugo..learned something new...

Bangla Hall of Fame Points: 3137 More actions · Answer 13

Bangla

Hall of Fame

Points: 3137

May 16, 2013 at 2:56 am

#1615655

Nice one Hugo..learned something....

Miles Neale SSChampion Points: 13147 More actions · Answer 14

I thought about this one too long but still got there. Nice!

Not all gray hairs are Dinosaurs!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 15

Another great question, thanks Hugo.

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