SQL Server Performance

How to use alias column name in where clause

Discussion in 'SQL Server 2005 General DBA Questions' started by baburk, Jun 24, 2008.

  1. baburk New Member

    Hi,
    How to use the alias column name ID the WHERE clause
    SELECT
    RoomType.RoomID AS ID,
    RoomType.PropertyID,
    RoomType.RoomName,
    RoomType.RoomDescription

    FROM RoomType

    WHERE ID = 116
    I am getting this error
    Msg 207, Level 16, State 1, Line 1
    Invalid column name 'ID'.
    baburk, Jun 24, 2008
    #2

  2. Madhivanan Moderator

    You cant use alias name directly in the WHERE clause
    You need to use the original column name/ expression
    WHERE Roomtype.RoomID=116
    Madhivanan, Jun 24, 2008
    #3

  3. Madhivanan Moderator

    and if you use complex expression and want to use it in where clause, use derived table
    Select * from
    (
    Your uery
    ) as t
    where alias_name=some_value
    Madhivanan, Jun 24, 2008
    #4

  4. rohit2900 Member

    SELECT * FROM
    (SELECT
    RoomType.RoomID AS ID,
    RoomType.PropertyID,
    RoomType.RoomName,
    RoomType.RoomDescription
    FROM RoomType) X

    WHERE ID = 116
    rOHIT
    rohit2900, Jun 24, 2008
    #5

  5. baburk New Member

    Hi,
    Thanks a lot it works fine.
    baburk, Jun 24, 2008
    #6

Share This Page