column_alias unusable?
-
I want to perform a simple select statement:
SELECT 1 as an_alias, (an_alias + an_alias)
but it produces the exception "Invalid column name 'an_alias'." !?
Of course there is a workaround:
SELECT an_alias, an_alias + an_alias
FROM (SELECT 1 as an_alias) x (an_alias)
How can I use the column_alias in the SELECT or WHERE clause?
I just do not want to repeat the whole expressions.
Artur
-
Well, I guess that's exactly what you have to do, if you like it or not
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
The generic form of a query that has columns that refer to other column aliases is:
select *, (an_alias + an_alias) as an_alias2, (col2 * 2) as an_alias3
from
(select (column1 * 2) as an_alias, col2
from table1
) as NestedTable
where an_alias = @somevalue
Though this would usually be too slow. For speed you only choice is to repeat the expressions.
If you want to refer to alias an_alias2 or an_alias3 in another column you have to nest the query again, like:
select *, (an_alias2 + an_alias3) as an_alias4
from
(select *, (an_alias + an_alias) as an_alias2, (col2 * 2) as an_alias3
from
(select (column1 * 2) as an_alias, col2
from table1
) as NestedTable
where an_alias = @somevalue
) as NestedTable2
Regards
Peter Tillotson
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply