Orlando Amador schrieb:
> 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
According to
http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/statements_103.htm#2075668
subquery factoring was available even in 9.0.1
However, you are not restricted to it, simple inline view should be
sufficient for what you are trying to achieve.
Best regards
Maxim
Received on Tue Jul 24 2007 - 15:42:32 CDT
