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
Received on Tue Jul 24 2007 - 15:29:16 CDT
