Use named user column in SQL 2005 in WHERE? Clause

Question

Can I name the custom column in the SELECT statement and the link that is in the WHERE clause without code duplication?

For instance:

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 save this code in only one place, but SQL Server 2005 forces me to duplicate a custom SELECT in the WHERE clause.

I believe this is possible in Microsoft SQL Server 2000, but not in 2005 anymore.

Thanks.

Answer 1

You can do this using the SUB SELECT or 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%' 

In one case where I know that you can use a column alias, you want ORDER BY this 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 

Answer 2

You can wrap it using sub-selection a little cleaner, for example:

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