column_alias unusable?

  • aszlejter

    SSC Enthusiast

    Points: 179

    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

  • Frank Kalis

    SSC Guru

    Points: 111183

    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]

  • Peter Tillotson

    Hall of Fame

    Points: 3325

    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 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply