Orlando Amador wrote:
> 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
See if you can do it by joining with an in-line view.
http://www.psoug.org/library.html and scroll down to "Inline Views."
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Jul 24 2007 - 15:35:19 CDT
