Reusing the result of an expression in select query

I need to compute a percentage by multiplying 2 columns like:

select A, B, ( A * B ) as rate from table;

Then I need to multiply "rate" with several other columns. So far this is what I have done:

select A, B, ( A * B ) as rate, (A * B) * C as Col1, (A * B) * D as Col2, (A * B) * E as Col3 from table;

I just repeat the same expression every time is needed. This has created a very big SQL query. I'm afraid that for maintenance and performance it would be a problem. Is there a way to save the result from "rate" and rewrite this as:

select A, B, ( A * B ) as rate, rate * C as Col1, rate * D as Col2, rate * E as Col3 from table;

With my limited knowledge of SQL I figure I could calculate the rate in a sub-query and then multiply rate against the rest of the columns of the same table. I figure that If this is done, it will scan the table twice and then join the results. I'm hopping there is a better way.

So what is the correct way to approach this kind of problems? Stored Procedures? Received on Tue Jul 24 2007 - 12:28:08 CDT