Hi,

I’ve been stuck getting a query running for a few days now. Clean somebody
help me please.
The problem is this.

Suppose I have 3 tables:

create table reservation
(
reservationid integer not null,
primary key (reservationid)
)

create table debt
(
debtid integer not null,
reservationid integer,
amount decimal (18, 3),
primary key (debtid)
)

create table payment
(
paymentid integer not null,
reservationid integer,
amount decimal (18, 3),
primary key (paymentid)
)

And this as testdata:

insert into reservation values(1);
insert into reservation values(2);
insert into reservation values(3);
insert into reservation values(4);

insert into debt values(1,1,100);
insert into debt values(2,2,200);
insert into debt values(3,3,300);
insert into debt values(4,4,400);
insert into debt values(5,1,10);
insert into debt values(6,2,20);
insert into debt values(7,3,30);
insert into debt values(8,4,40);

insert into payment values(1,1,10);
insert into payment values(2,2,20);
insert into payment values(3,3,30);
insert into payment values(4,4,40);
insert into payment values(5,1,20);
insert into payment values(6,2,40);
insert into payment values(7,3,60);
insert into payment values(8,4,80);


I would like to have a result from a select that is:
Column 1: reservationid
Column 2: total debt for a reservation id
Column 3: total payment for a reservation id
Column 4: total debt-payment for a reservation id

I also would like to be able to use a where clause to select reservationid’s
where the amount due is larger then x.

My first attempts where using selects like:

select r.reservationid, sum(p.amount) from reservation r
join payment p on p.reservationid = r.reservationid
group by r.reservationid

this gives:
reservationid sum
1 30
2 60
3 90
4 120

But I was not able to combine this with the debts.

Then I tried to join the two selects this didn’t work.

Then I tried stored procedures:

CREATE PROCEDURE calcDebtsForRelation(objectid integer)
RETURNS( debt DECIMAL(18, 3))
AS
BEGIN
select sum(d.amount) from reservation r
join debt d on d.reservationid = r.reservationid
where r.reservationid = :objectid

INTO :debt;
SUSPEND;
END

I could use the stored procedure like select * from calcDebtsForRelation(1).
What is wanted was something like:
select * from reservation r, calcDebtsForRelation(r.reservationid)

expecting it to give:
1 110
2 220
3 330
4 440

But it gives: not current record for fetch operation on line 1: select *
from reservation r, calcDebtsForRelation(r.reservationid)

Now I am stuck.

Could somebody please help.

Thank you,

Henk van der Meer.