Use a named custom column in SQL 2005 in WHERE clause?

Question

Can I name a custom column in the SELECT statement and reference that in the WHERE clause without duplicating code?

For example;

SELECT RIGHT(REPLICATE('0', 5) + RTRIM(SOME_ID)), 5) AS DISPLAY_ID
FROM dbo.MY_TABLE
WHERE DISPLAY_ID LIKE '%005%'

Only much more complicated. I want to maintain this code in one place only but SQL Server 2005 forces me to duplicate the custom SELECT in the WHERE clause.

I believe this was possible in Microsoft SQL Server 2000 but no longer in 2005.

Thanks.

Answer 1

You can do this using either a SUB SELECT or a CTE function

SELECT  *
FROm    (
            SELECT RIGHT(REPLICATE('0', 5) + RTRIM(SOME_ID), 5) AS DISPLAY_ID 
            FROM MY_TABLE 
        )   sub
WHERE   DISPLAY_ID LIKE '%005%' 

OR

;WITH Tbl AS(
        SELECT RIGHT(REPLICATE('0', 5) + RTRIM(SOME_ID), 5) AS DISPLAY_ID 
        FROM MY_TABLE 
)
SELECT  *
FROM    Tbl
WHERE   DISPLAY_ID LIKE '%005%' 

One of the times that I am aware of that you can use the column alias, is when you wish to ORDER BY that column alias.

EDIT:

Multiple CTE blocks

DECLARE @MY_TABLE TABLE(
        SOME_ID INT
)

DECLARE @Your_TABLE TABLE(
        SOME_ID INT
)

;WITH Table1 AS(
        SELECT *
        FROM @MY_TABLE
),
Table2 AS(
        SELECT *
        FROM @Your_TABLE
)
SELECT  *
FROM    Table1 t1 INNER JOIN
        Table2 t2 ON t1.SOME_ID = t2.SOME_ID