Use a new calculated column name in a calculation

[DataMiner]

Hi,
In MS Access I can use a calculated column name in a later calculation, as I am using "MyValue1" here:

SELECT [A_Cutoff]+[b_cutoff] AS MyValue1, 2*[myvalue1] AS MyValue2
FROM Constants;

When I try to do this in SQL server the 2*[myvalue1] part fails because it does not recognized MyValue1 as a column name.

Is there a way to make this work?

Of course I know in this simple example I can just replace 2*[myvalue1] with 2*([A_cutoff]+[B_Cutoff]). But in the real world, the calculation for MyValue1 is MUCH more complex and gets used in several places within the query. Makes the SQL very hard to read unless I can alias the column name like I do in Access.

 

[namliam]

Use a subselect... Like so

SELECT MyValue1, 2*[myvalue1] AS MyValue2
FROM ( SELECT [A_Cutoff]+[b_cutoff] AS MyValue1 from Constants);

 

[SQL_Hell]

Hi,

No real need for derived table query, you could of just done the following

SELECT [A_Cutoff]+[b_cutoff] AS MyValue1, 2*([A_Cutoff]+[b_cutoff]) AS MyValue2
FROM Constants