Reuse of calcuated columns in a single query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Reuse of calcuated columns in a single query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-07 : 08:05:41

Scott writes "I'm wonderng if it is possible to reuse a calucated column within a single query. An exampl would probably be helpful.

SELECT LEFT(SSN, 3) AS SSN3, RIGHT(SSN3,2) AS SSN2
FROM Users

When I try that I get an error that says:
Invalid column name 'SSN3'

Of course I could do the following:
SELECT LEFT(SSN, 3) AS SSN3, RIGHT(LEFT(SSN, 3), 2) AS SSN2
FROM Users

However, this version seems a little wasteful of keystrokes and performance. Then again maybe the query plan generator is smart enough to recognize that a the LEFT(SSN, 3) is being used twice and only compute it once. Just so everyone is aware... the example I've presented is a simplified version of what I'm working on. The actual example is much larger and computationally intensive.

So is there a way to reuse columns in a single query? Or does it not matter (ie. query plan optimizes the 2 identical expressions into 1)?

Thanks!"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-07 : 08:10:21

You need to use Derived table

SELECT SSN3,RIGHT(SSN3,2) AS SSN2 FROM
(
SELECT LEFT(SSN, 3) AS SSN3 FROM Users
) T

OR

SELECT LEFT(SSN, 3) AS SSN3, RIGHT(LEFT(SSN, 3),2) AS SSN2
FROM Users

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources