Using alias in Sql Server 2005

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

General SQL Server Forums

New to SQL Server Programming

Using alias in Sql Server 2005

Author

Topic

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-04-25 : 09:22:04

Hi All
I am getting error in Sql server 2005 when using

select getdate() as a, a+2 from dual

But when using select getdate(), getdate()+2 from dual It is working fine
If this so then why can't alias works

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-25 : 09:24:58

Aliases you define in the SELECT list are not available anywhere except in ORDER BY clause. It is not available even to other columns in the select list. So you would need to use

SELECT getdate() as a, getdate()+2 from dual

goodman2253
Yak Posting Veteran

88 Posts

Posted - 2012-04-25 : 09:26:16

any substitute

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-25 : 09:34:57

You can use sub queries or common table expressions, but those add their own baggage which may not be worth the effort if the only purpose is to be able to use the alias. For example either of these:

;WITH cte AS( SELECT getddate() AS a FROM dual)
SELECT a, a+2 FROM cte;
--------
SELECT a, a+2 FROM
(
	SELECT GETDATE() AS a FROM dual
)s

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-04-25 : 09:44:13

...and "FROM dual" is Oracle syntax and will not work in sql server. Just omit it the FROM completely:

SELECT getdate() as a, getdate()+2

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources