Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Assigning categories to values 2


Assigning categories to values 2

Author
Message
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)SSCrazy Eights (8.4K reputation)

Group: General Forum Members
Points: 8352 Visits: 11594
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. Sad


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Revenant
Revenant
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5839 Visits: 4721
It was so simple that I suspected a trick. :-)

Thanks, Hugo!
johnf_amic
johnf_amic
SSC-Addicted
SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)SSC-Addicted (420 reputation)

Group: General Forum Members
Points: 420 Visits: 277
I suspected a question like this would be coming soon, so I practiced yesterday! Cool
Christian Buettner-167247
Christian Buettner-167247
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: 2953 Visits: 3889
I was also looking for the trickSmile

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
Narud
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1560 Visits: 502
Very positive knowledge acquired.

Thanks Hugo!
Ford Fairlane
Ford Fairlane
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1862 Visits: 836
Good hump day QotD thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6287 Visits: 1407
Thanks Hugo..learned something new...



Bangla
Bangla
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1627 Visits: 180
Nice one Hugo..learned something....
Miles Neale
Miles Neale
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2690 Visits: 1694
I thought about this one too long but still got there. Nice!

Not all gray hairs are Dinosaurs!
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16546 Visits: 13210
Another great question, thanks Hugo.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
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