Reuse calculated AS fields in other fields

Question

Post reply

Reuse calculated AS fields in other fields

Paul Maddox

Grasshopper

Points: 12
More actions
August 8, 2008 at 4:17 am

#195627

This has been a ongoing gripe of mine that until now I have been able to get around. The problem is not being able to reference calculated fields in other fields. Eg./
SELECT 1+2 as Three, Three+3 AS Six
Is there way to get the above to work? (The issue being the second field has no knowledge of what Three is.)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

You may use derived tables:

SELECT Three, Three+3 AS Six

FROM (

SELECT 1+2 as Three

) DT

_____________
Code for TallyGenerator

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 2

Paul:

The "short" answer to your question is that MS SQL Server does not presently support that kind of alias. A work around such as the previous post is required or you might simply want to in-line compute the expression with something like:

select 1+2 as Three, (1+2)+2 as Six

Paul Maddox Grasshopper Points: 12 More actions · Answer 3

Thanks for the answers guys.

I have inlined my expressions in the past. The problem is, of course, that when they get large it can gets very difficult to read/maintain.

Kent Waldrop Hall of Fame Points: 3064 More actions · Answer 4

Please give this page a look:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273443

If this is something that you like please comment on it and I will also do the same.