Reusing in a SELECT statement - 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)

Reusing in a SELECT statement

Author

Topic

Stef
Starting Member

5 Posts

Posted - 2001-11-27 : 07:52:52

Hi,

I have to write a query of the following kind :

SELECT A * B, 2*(A*B) FROM TABLE1

Where TABLE1 has A and B columns. (Of course, in my problem these columns are much longer expressions).
So I would like to do some like the following :

SELECT (A * B) alias1, 2*alias1 FROM TABLE1

using an alias. Can I do it that way (apparently not) ?

If not, what's the way to do it ?

Thanx

Stephane

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-27 : 09:12:37

select alias1 , 2*alias1 as alias2
from (select a*b as alias1 from table1 ) as s

Stef
Starting Member

5 Posts

Posted - 2001-11-27 : 10:04:57

Following this advice :

quote:

select alias1 , 2*alias1 as alias2
from (select a*b as alias1 from table1 ) as s

I tried :

select tot, tot*2 from (select Amount1+Amount2 as tot from Prices)

But I get the enigmatic : "incorrect syntax near the keyword 'select'"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2001-11-27 : 10:28:12

A derived table (subquery) MUST be aliased. Lars included this in his example but it was dropped from your code:

select tot, tot*2 from (select Amount1+Amount2 as tot from Prices) AS A

There is a recent article on using derived tables:

http://www.sqlteam.com/item.asp?ItemID=6692

Stef
Starting Member

5 Posts

Posted - 2001-11-29 : 05:59:53

quote:

A derived table (subquery) MUST be aliased. Lars included this in his example but it was dropped from your code:

select tot, tot*2 from (select Amount1+Amount2 as tot from Prices) AS A

There is a recent article on using derived tables:

http://www.sqlteam.com/item.asp?ItemID=6692

I didn't write it in my mail, but I put this ALIAS when trying. But I still got this same error 'Invalid syntax near 'SELECT'

I didn't mention before that I was using a SYBASE database. Is it a problem ?

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2001-11-29 : 08:23:19

Well, I have not looked at tbe latest Sybase version, but they did not support derived queries in version 10.

You could solve this by creating a view for the "inner" select

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources