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) + R开发者_运维问答TRIM(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.
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
You can wrap it using a subselect a bit cleaner, like this:
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%'
精彩评论