SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Using alias in Sql Server 2005		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

goodman2253
Yak Posting Veteran

83 Posts
Posted - 04/25/2012 :  09:22:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/25/2012 :  09:24:58  Show Profile  Reply with Quote
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
Go to Top of Page

goodman2253
Yak Posting Veteran

83 Posts
Posted - 04/25/2012 :  09:26:16  Show Profile  Reply with Quote
any substitute
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 04/25/2012 :  09:34:57  Show Profile  Reply with Quote
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
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3241 Posts
Posted - 04/25/2012 :  09:44:13  Show Profile  Reply with Quote
...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/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000