Re: Alternative to a correlated query?

From: Conan Farrell <conan_at_(remove_this)as-if.com>
Date: 27 Oct 1999 09:52:59 +0100
Message-ID: <01bf2050$acc92880$b501ce0a@Cfarrell.snn.ie.sykes.com>

I had a similiar problem before where in order to find someones last pay point I had to preform 5 correlated queries. obviously performance was'nt great. What i did was to write a function to return the rowid of the record I wanted.
It the example you have outlined write a function that accepts the partnerid as an input variable. Create a cursor that returns a rows for that partner, sorted by date descending. Fetch the first row. you now have the rowid of the record you need. to use the function in a query simply add the line

APPROVAL_TABLE.rowid = function(partner_table.ID)

HTH --
Conan Farrell
Oracle DBA Received on Wed Oct 27 1999 - 03:52:59 CDT