SQL Server Reference a Calculated Column

Question

I have a select statement with calculated columns and I would like to use the value of one calculated column in another. Is this possible? Here is a contrived example to show what I am trying to do.

SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25

Answer 1

No.

All the results of a single row from a select are atomic. That is, you can view them all as if they occur in parallel and cannot depend on each other.

If you're referring to computed columns, then you need to update the formula's input for the result to change during a select.

Think of computed columns as macros or mini-views which inject a little calculation whenever you call them.

For example, these columns will be identical, always:

-- assume that 'Calc' is a computed column equal to Salaray*.25
SELECT Calc, Salary*.25 Calc2 FROM YourTable

Also keep in mind that the persisted option doesn't change any of this. It keeps the value around which is nice for indexing, but the atomicity doesn't change.

Answer 2

You can't "reset" the value of a calculated column in a Select clause, if that's what you're trying to do... The value of a calculated column is based on the calculated column formulae. Which CAN include the value of another calculated column.... but you canlt reset the formulae in a Select clause... if all you want to do is "output" the value based on two calculated columns, (as the syntax in your question reads" Then the "[calcval2]"
in

SELECT [calcval1] = CASE Statement, [calcval2] = [calcval1] * .25

would just become a column alias in the output of the Select Clause.

or are you asking how to define the formulae for one calculated column to be based on another?

Answer 3

Two ways I can think to do that. First understand that the calval1 column does not exist as far as SQl Server is concerned until the statment has run, therfore it cannot be directly used as shownin your example. SO you can put the calculation in there twice, once for calval1 and once as substitution for calcval1 in the calval2 calculation. The other way is to make a derived table with calval1 in it and then calculate calval2 outside the derived table something like:

select calcval1*.25 as calval2, calval1, field1, field2 from (select casestament as cavlval1, field1, field2 from my table) a

You'll need to test both for performance.

Answer 4

Unfortunately not really, but a workaround that is sometimes worth it is

SELECT [calcval1], [calcval1] * .25 AS [calcval2]
FROM (SELECT [calcval1] = CASE Statement FROM whatever WHERE whatever)