Re: Reusing the result of an expression in select query

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."

-- 
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