At 09:24 AM 12/10/2007, you wrote:

>I have to correlate two sets of data. One is a list of payments and
>the other is a list of invoices, both ordered by date. A given
>payment may be allocated to multiple invoices. Also, a given
>invoice may consume multiple payments. For a variety of reasons,
>payments were never associated with an invoice when they were first
>entered, and it's too late now (IOW, I can't readily change the
>structure of these tables and even if I could, I'd still have to do
>this to fill in the missing data).
>
>I want to scan the two sets, fetching an item from each and deciding
>how they match up. As each is consumed, I need to fetch another one
>of that kind, producing in the end a sum of payments within a given
>period.
>
>This is basically a merge problem, but I don't see a way to do it in
>PSQL because it looks to me like you have to read all of one set
>before you can do anything with the other one.

No, quite untrue. All you need is the base set (master set, whatever
you want to call it) with enough fields to enable search links of one
form or another. The advantage of using a SP to do this kind of job
is precisely because you can embed cursor loops as deeply as you need
to. You can use the exposed FOR...SELECT...INTO syntax and/or
optimise performance by using named cursors.

>Does anyone have any suggestions as to how this could be done using
>PSQL?

How detailed do you need to get?

>Or SQL for that matter,

PSQL is SQL. If you mean extracting a dynamic set, yes, it's doable
too, more so in Fb 2.0 than 1.5 because of EXECUTE BLOCK and derived tables.

>say with an SP or a UDF in the join condition?

Don't know what you think a UDF could do. UDFs are functions. A
function returns a scalar result.

Joining to SPs is technically possible but almost totally senseless
from the POV of logic, performance and, in some conditions,
consistency. If you have to join to a SP it means you need another SP.

Use your programmer skills and write a proper stored procedure that
does the whole job without wasting CPU cycles or causing unnecessary
lockups. But do provide more info about your logic loops and
inter-relationships if you need to understand more...

./heLen


./heLen