Referencing a computed column in a derived column

[jlitondo]

Is there a way to do this, for example:

SELECT a,b,(a+b) as ab, (ab/2000) as d FROM Table1.

The problem in the above occurs at column d, whereby SQL Server throws out the error message: Invalid Column Name ab

I tried passing ab to a function and having it compute and return the result as d but it doesn't jive.

 

[amorous]

Why cant you do this instead..

SELECT a,b,(a+b) as ab, ((a+b)/2000) as d FROM Table1

VJ

 

[jlitondo]

Well, I could but the expression (a+b) is really a much more complex computation that would be refered to in several more derived columns, and I was trying to save myself from re-typing the same expression over and over.

 

[TJRTech]

You could retype and recalc the value again and again...

OR you could create a view that has the calculated column and use the view instead, repeating the view column as many times as needed.

I did this recently in computing an integer FK (don't ask), that I then needed to use in a table join. Just made the join a lot cleaner looking because the computation had a CASE within it.

TR