Re: Reusing the result of an expression in select query

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
.

Relevant Pages

  • Re: Reusing the result of an expression in select query
    ... DA Morgan wrote: ... This has created a very big SQL query. ... Looks like With clause is for 10.2? ... Any suggestions for Oracle 9? ...
    (comp.databases.oracle.server)
  • Re: Reusing the result of an expression in select query
    ... This has created a very big SQL query. ... Go to Morgan's Library at www.psoug.org and scroll down to "WITH." ... Looks like With clause is for 10.2? ... Any suggestions for Oracle 9? ...
    (comp.databases.oracle.server)
  • Re: Locking question when using Select clause with For Update and Skip locked
    ... table for update and skipped the rows locked by other sessions on same ... select top 1 empno from emp ... (we also have order by clause but will remove it here for simplicity) ... Now on Oracle system it is ...
    (comp.databases.oracle.server)
  • Re: Outer join by (+)
    ... Robert Klemme wrote: ... I can read the standard SQL just fine, because that's what I'm used to. ... WHERE clause excludes. ... This still leaves the issue of lesser expressiveness for the proprietary Oracle syntax. ...
    (comp.databases.oracle.server)
  • Re: How top actually works
    ... So the next one shows that order by clause has affected the result set ... sort these N rows according to my order by clause. ... Ie. you are telling SQL Server ... This is much the same as with rownum in Oracle, ...
    (comp.databases.ms-sqlserver)