Re: Reusing the result of an expression in select query

DA Morgan wrote:
Orlando Amador wrote:
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?

And your Oracle version is?

Look at using a WITH clause:

Go to Morgan's Library at www.psoug.org and scroll down to "WITH."

Thanks for the quick response. This is for Oracle 9.2. Looks like With clause is for 10.2?

I'll keep that in mind for when we upgrade. I'm also bookmarking your page, looks full of resources.

Any suggestions for Oracle 9?

Saludos,
Orlando
.

Relevant Pages