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.

Textile

What's a "custom column", and what is the other kind called? – Obryan

I doubt that is the accepted term. I just mean a column on which I perform some manipulation before returning the data. – Textile

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

Deafmute

Be carefull of nesting your subselects too deep. This might not only give you a performance degrade, but might make it very hard to maintain. Think of temp/var tables, or cte queries rather... – Deafmute

I'm not familiar with CTEs and I'll only need one SUB SELECT in this case. Though, looking at the documentation, it seems like CTEs may be the way to go. Might clean up my code a bit too. – Textile

OK, CTEs are much more pleasant. Thanks for introducing them to me. – Textile

You are aware that you can hare multiple CTE selects on the go. If you wish to know more, and cant find the docs I will gladly assits X-) – Deafmute

Do you mean you can have two CTE blocks and use both in the following SELECT? – Textile

Yes, that is true, you can have 2 CTE blocks – Deafmute

Answer 2

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%'

Stammel

This is an already massive SELECT. Will wrapping it in another impact performance much? – Textile

@Orange Kid: Using a subquery will guarantee another table scan - while duplicated, performing in one query will only perform one table scan - making it more performant. – Methane

@OMG Ponies - Woops, been in oracle too long, fixed. – Stammel

HEY PONIES, have you noticed, your pic does not look any different today >:-) – Deafmute

@Nick Craver, Your answer does not use a subquery. A subquery is a SELECT query that returns a single value and is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. See Subquery Fundamentals You are using a derived table, also called an inline view, of which you name "SubTable" – Schematize

@KM - Updated for accuracy, but that's not an accurate definition of subquery, it can return many values not just a single value. Example: Where ID IN (Select ID from Employees) – Stammel

that is the definition straight from the msdn on subqueries (see link in my comment). – Schematize

@KM - Fair enough, but it's not an accurate one, e.g. my previous example :) – Stammel

your exact WHERE ID IN... example is in the doc from the link Subquery Fundamentals, look up WHERE (Transact-SQL) msdn.microsoft.com/en-US/library/ms188047%28v=SQL.90%29.aspx, a where clause contains <search_conditions>, then look up Search Condition (Transact-SQL) msdn.microsoft.com/en-us/library/ms173545.aspx, this applies to your example in your comment: expression [ NOT ] IN ( subquery | expression [ ,...n ] ). – Schematize

Answer 3

instead of as ColumnName just use ColumName

AS keyword make column unavailable in query clause

Making