If you are looking for a SQL Server solution, I recommend the following. Create a View that performs the first level of calculations. Use that View in another View or query to perform the next level of calculations and so forth.
The reason to do this is that SQL does not allow you to use the result of a calculation in another calculation. For example, the next query is not legal in SQL Server.
Select
ProdCd, Qty, Price,
ExtPrice=Qty*Price,
TaxAmt=ExtPrice*0.08 /* This line isn't legal */
From Table
Of course, the following can be done but it becomes lengthy and complex when numerous calculations are involved.
Select
ProdCd, Qty, Price,
ExtPrice=Qty*Price,
TaxAmt=Qty*Price*0.08 /* calculate extended price again */
From Table
As recommended, you can create a View to perform the first calculation and another query can use that View and perform the next level of calculation. If you have multiple levels of calculations, you can create multiple levels of Views.
Create View vExtendedPrice As
Select
ProdCd, Qty, Price,
ExtPrice=Qty*Price
From Table
The next query would be...
Select
ProdCd, Qty, Price,
ExtPrice, TaxAmt=ExtPrice*0.08
From vExtendedPrice
Here is another technique you can use. It is useful if you only have a few columns and calculations. Create sub queries and select data from the sub queries.
Select
ProdCd, Qty, Price,
ExtPrice, TaxAmt=ExtPrice*0.08
From
(Select
ProdCd, Qty, Price,
ExtPrice=Qty*Price
From Table) As e
The calculation will be done in the inner query and the outer query can then use the calculated value by name. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.
