Using alias in an expression

Gunnar Vøyenli

Hi
I tried to use the alias a1 for the column f1 like this, but it fails:

create table a (f1 int not null primary key)
insert into a (f1) values (2)

select f1 a1 from a where a1 = 2

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'a1'.

In my real situation f1 is a "pretty complex" calculation, and a1 is an
alias of that.
I wanted to use the alias in the query instead of repeating the calculation
several times.

If this use of alias is not legal, then is there a way to get around this?

TIA
Gunnar Vøyenli
EDB-konsulent as
NORWAY

Jul 20 '05 #1

David Portas

Put your aliased column in a derived table query.

SELECT a1
FROM
(SELECT f1 AS a1
FROM A) AS A
WHERE a1 = 2

--
David Portas
SQL Server MVP
--

Jul 20 '05 #2

Gunnar Vøyenli

Thanks alot, David!
Now, its working fine!
-Gunnar
"David Portas" <RE****************************@acm.org> wrote in message
news:Fa********************@giganews.com...

Put your aliased column in a derived table query.

SELECT a1
FROM
(SELECT f1 AS a1
FROM A) AS A
WHERE a1 = 2

--
David Portas
SQL Server MVP
--

Jul 20 '05 #3