Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Assigning categories to values 2 Expand / Collapse
Author
Message
Posted Wednesday, May 15, 2013 9:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:14 PM
Points: 5,977, Visits: 8,237
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1453179
Posted Wednesday, May 15, 2013 10:05 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, August 22, 2014 8:50 AM
Points: 4,425, Visits: 3,417
It was so simple that I suspected a trick.

Thanks, Hugo!
Post #1453207
Posted Wednesday, May 15, 2013 10:38 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 9:41 AM
Points: 378, Visits: 267
I suspected a question like this would be coming soon, so I practiced yesterday!
Post #1453222
Posted Wednesday, May 15, 2013 11:01 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:47 AM
Points: 2,840, Visits: 3,872
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
Post #1453230
Posted Wednesday, May 15, 2013 12:26 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, May 2, 2014 4:11 PM
Points: 645, Visits: 377
Very positive knowledge acquired.

Thanks Hugo!
Post #1453250
Posted Thursday, May 16, 2013 1:53 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 11, 2014 12:00 AM
Points: 1,691, Visits: 757
Good hump day QotD thanks

Hope this helps...

Ford Fairlane
Rock and Roll Detective





Post #1453356
Posted Thursday, May 16, 2013 2:51 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:30 AM
Points: 5,344, Visits: 1,388
Thanks Hugo..learned something new...


Post #1453372
Posted Thursday, May 16, 2013 2:56 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 22, 2014 2:42 AM
Points: 797, Visits: 153
Nice one Hugo..learned something....
Post #1453375
Posted Thursday, May 16, 2013 2:27 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 2,299, Visits: 1,356
I thought about this one too long but still got there. Nice!


Not all gray hairs are Dinosaurs!
Post #1453779
Posted Friday, May 17, 2013 3:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:26 AM
Points: 13,622, Visits: 10,514
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 LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1453904
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse