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.

What's a "custom column", and what is the other kind called? — John Saunders
– John Saunders, Commented Apr 2, 2010 at 5:34
I doubt that is the accepted term. I just mean a column on which I perform some manipulation before returning the data. — Orange Kid
– Orange Kid, Commented Apr 2, 2010 at 14:39

Adriaan Stander · Accepted Answer · 2010-04-02 05:30:00Z

10

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

edited Apr 2, 2010 at 5:30

answered Apr 1, 2010 at 17:51

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adriaan Stander Over a year ago

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...

Orange Kid Over a year ago

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.

Orange Kid Over a year ago

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

Adriaan Stander Over a year ago

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-)

Orange Kid Over a year ago

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

|

Nick Craver · Accepted Answer · 2010-04-01 18:10:36Z

3

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

edited Apr 1, 2010 at 18:10

answered Apr 1, 2010 at 17:50

Nick Craver

631k138 gold badges1.3k silver badges1.2k bronze badges

9 Comments

Orange Kid Over a year ago

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

OMG Ponies Over a year ago

@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.

Nick Craver Over a year ago

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

Adriaan Stander Over a year ago

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

KM. Over a year ago

@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"

|

Swapnil Thube · Accepted Answer · 2023-12-04 15:02:46Z

0

instead of as ColumnName just use ColumName

AS keyword make column unavailable in query clause

answered Dec 4, 2023 at 15:02

Swapnil Thube

1331 silver badge5 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

6 Comments

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related