carl.pedersen@dartmouth.edu (Carl Pedersen) (05/25/89)
I'm working on an SQL problem using ORACLE for which I have
thought of two solutions, both of which have drawbacks.
The system tracks loans. A borrower can have multiple loans
outstanding. The different loans need to be tracked separately,
but the borrowers make a single payment to cover all amounts
due. The problem I am working on concerns the case where the
borrower makes a payment which is less than the sum of all the
amounts due. In that case, I want to allocate the payment
proportionally to each loan according to the amounts due.
Suppose I have a table called DUE, with three columns: BORROWER,
LOAN, and AMOUNT and a table called PAYMENT with two columns,
BORROWER and AMOUNT and a view called TOTAL_DUE, created as
follows:
create view TOTAL_DUE as
select BORROWER, sum(AMOUNT) AMOUNT
from DUE group by BORROWER;
Then, I can allocate the payment as follows:
update DUE set AMOUNT =
( select A.AMOUNT - (A.AMOUNT*PAYMENT.AMOUNT) / TOTAL_DUE.AMOUNT
from DUE A, PAYMENT, TOTAL_DUE
where A.BORROWER = DUE.BORROWER and A.LOAN = DUE.LOAN
and PAYMENT.BORROWER = DUE.BORROWER
and TOTAL_DUE.BORROWER = DUE.BORROWER );
This is what Oracle calls a "correlated update". I've heard
it's non-ANSII, but it works in more than one SQL implementation
I know of. It works exactly right if the payment amount matches
the sum of the amounts due. In general, though, if the payment
is less than the total due, the remaining total due will be
slightly off, due to rounding.
My two solutions are as follows:
1) Compute the discrepancy, pick one loan at random and adjust
its amount due to make the total match the payment. I think
this is easy, but I also think it's ugly.
2) Allocate to each loan separately in a sequential procedure.
Subtract the amount paid to each loan from the total due
before computing the amount due to the next loan. When you
reach the last loan, the amount due for it will be equal to
the total for all remaining loans so that the rest of the
payment will be allocated with nothing left over.
Number 2 is the approach I'd use in a 3GL, and I believe that it
is correct in the sense that the payment is allocated as fairly
as possible. The main problem with this approach is that as far
as I can tell, it requires a loop containing multiple SQL
statements. Since I don't have Oracle's PL/SQL (yet) or any of
the preprocessors, I'd have to do this with some sort of kludge.
That, in combination with the fact that the SQL statements
themselves would be complicated, means that this approach would
probably be slow and would definitely be very hard to understand.
Anybody see a way to do this with a fixed (hopefully small)
number of SQL statements that has the same effect as number 2?