Aliasing in Group By statements
-
I'm a relatively new convert from Informix to SQL Server and still learning the differences between the two softwares. I've been boggled for awhile now in how aliased column names in the select portion of a SQL statement are recognized in order by clauses but not in the group by. Is there a way to get around this? This is both annoying and illogical to me, especially when having to copy an entire CASE statement in the group by rather than just using the alias.
-
Johnathon,
The way around it is to embed the case statement in an inline view ( or derived table like others call it)
ex:
select x,y,z,...
from
( select case when ......... as x,
case when ....... as y,
case when ......... as z,...
from
OriginalTable ) as D
group by x,y,z
order by x,y,z
The optimizer will take care of the the seemingly redundant structure and should create the same plan as if you use the case in the group by
Cheers,
* Noel -
Thanks! That does work indeed. I still don't understand why you just simply can't alias in the group by but I guess I'll just accept it and move on
You can alias in the group by in Informix, btw.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply