Referencing Columns w/in Same View

[rickj65]

In Access, when you write a query having a field that calculates values you can refer to that same field by its name elsewhere in the query without having to repeat the calculation.

For example, in the query below, in order to calculate the Taxable Amount, I can refer to the "Total" field within the same query...

SELECT Qty, Price, (Qty * Price) AS Total, (Total * Tax) AS TaxAmount
FROM tblSalesOrder

Within SQL Server I must recalculate the Total within the TaxAmount calculation, rather than simply referring to the Total field...

SELECT Qty, Price, (Qty * Price) AS Total, (Qty * Price * Tax) AS TaxAmount
FROM tblSalesOrder

Is there any simple way around this limitation so that I can include multiple calculation within the same query/view? The query I'm writing is very large and complex with over 150 fields and many of the figures rely on previously calculated fields. Having to repeat initial calculations to be used in subsequent calculations just seems counterproductive and makes the query very difficult to interpret.

I know I can create a view that does the initial calculations and then nest that view in a subsequent view, but to accomplish this I might require 4 or 5 levels of nesting...again, counterproductive.

TIA,
Rick

 

[JamesLean]

You've just about covered all your options in your question! As you say, you can't refer to aliases (other than in the ORDER BY clause) plus you suggest creating views. Can't think of any other ways...

--James

 

[smin]

Unfortunately SQL Server does not allow aliases being reused in the same query. It would certainly shorten our select statement if we could do that.

The only way around that i know is to write sp and use variables.

 

[sunila7]

Hi,

U can write a query like this

SELECT QTY,Price,Total,(Total * Tax) AS Taxamount FROM
(SELECT Qty, Price, (Qty * Price) AS Total, Tax
FROM tblSalesOrder) DerivedTable

Not sure if it will help... bu u can avoid multiple views with it.

Sunil

 

[rickj65]

Thank you all for letting me know I'm basically screwed

I was just wondering if I was missing something obvious.

 

[SQLSister]

YOu can also do calculations in a UDF and then refernce the result of the udf in the select statement. If you are using SQL 2000.

 

[rickj65]

Unfortunately I'm using SQL 7.0.
Thanks.