When can I use an Alias? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

When can I use an Alias?

Author

Topic

DOlivastro
Starting Member

41 Posts

Posted - 2006-03-24 : 16:31:49

It seems that I can't use an alias name in a WHERE clause, but I can in a ORDER BY clause. For example, this is wrong

Select min (Name) as First_Name
from T_Employees
group by Name
where First_Name = 'Dominic'
order by First_Name

I have to change the where clause to

where min (Name) = 'Dominic'

Is there any reason for this? It is especially provoking whent the where clause needs to repeat a full "CASE WHEN" statement.

Dom

nr
SQLTeam MVY

12543 Posts

Posted - 2006-03-24 : 21:22:58

Just something you have to remember (should be having not where).
You can think of it as executing the having clause before generating the resultset so the alias isn't available whereas the order by is executed on the result so the alias is available.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-03-27 : 07:33:46

alternative version....(I think this should work)
Select min (Name) as First_Name
from T_Employees
where First_Name = 'Dominic' <---moved here
group by Name
order by First_Name

pootle_flump

1064 Posts

Posted - 2006-03-27 : 08:00:56

quote:
Originally posted by DOlivastro
It is especially provoking whent the where clause needs to repeat a full "CASE WHEN" statement.

Hi Dom
If you have loads of logic you don't fancy repeating in several clauses you can select from a derived table instead.

For example imagine this with one of your long case statements instead of an aggregate:


SELECT First_Name
FROM (
Select min (Name) as First_Name
from T_Employees
group by Name
) AS DerivedT
WHERE First_Name = 'Dominic'

HTH

BTW Andrew - I'm afraid your query wouldn't work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-27 : 08:44:57

quote:
Originally posted by AndrewMurphy

alternative version....(I think this should work)
Select min (Name) as First_Name
from T_Employees
where First_Name = 'Dominic' <---moved here
group by Name
order by First_Name

That wont work until you use Derived table

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-27 : 09:01:01

[code]select 'Dominic' as First_Name[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

DOlivastro
Starting Member

41 Posts

Posted - 2006-03-27 : 10:55:34

Pootle_Flump had the best answer for my purposes. Thanks for that.

Khtan, I hope you understand that I was just using a quick example. Perhaps not a very good one (as you noted), but my point can be made by other queries.

I guess I'm still not sure WHY we have to go through derived tables. But I guess NR made the correct point -- you can only use an alias in clauses that are interpreted after the result set returns.

Again, thanks to everyone.

Dom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources