Hi Sergio, sorry for answering late, but I didn't have any time to try
out my idea before now...

Since I'm normally using Firebird 1.5, I've no experience in doing
anything similar to this, but I decided to see whether the WITH
RECURSIVE statement of Firebird 2.1 could be abused to solve your
question. I made the assumption that invoices were always executed
before payments, and decided that the primary key of the two tables
should be set through one common generator (as opposed to a separate
generator for each table) to avoid payments and invoices with the same
primary key value. This way, several payments/invoices could happen on
the same day.

The fact that you have separate tables for payments and invoices
complicates the SQL a bit, as do my suggestion to allow several
payments/invoices for each customer each day.

The table/trigger definitions that I used were:

CREATE TABLE INVOICES
(
ID INTEGER NOT NULL,
CUSTOMERID INTEGER,
INVOICEDATE DATE,
AMOUNT INTEGER,
CONSTRAINT PK_INVOICES PRIMARY KEY (ID)
);

SET TERM ^^ ;
CREATE TRIGGER INVOICES_ID_GEN FOR INVOICES ACTIVE BEFORE INSERT
POSITION 0 AS
begin
if ((new.ID is Null) or (New.ID=0)) then
NEW.ID = GEN_ID(GEN_INVOICEPAYMENT, 1);
end
^^
SET TERM ; ^^

CREATE TABLE PAYMENTS
(
ID INTEGER NOT NULL,
CUSTOMERID INTEGER,
PAYDATE DATE,
AMOUNT INTEGER,
CONSTRAINT PK_PAYMENTS PRIMARY KEY (ID)
);

SET TERM ^^ ;
CREATE TRIGGER PAYMENTS_ID_GEN FOR PAYMENTS ACTIVE BEFORE INSERT
POSITION 0 AS
begin
if ((new.ID is Null) or (New.ID=0)) then
NEW.ID = GEN_ID(GEN_INVOICEPAYMENT, 1);
end
^^
SET TERM ; ^^

After filling these tables with some test data, I used this recursive
query to get the output that you desire:

WITH RECURSIVE UNIONED_TABLES (ID, CUSTOMERID, WHAT, IPDATE, AMOUNT)
AS (SELECT ID, CUSTOMERID, CAST('INVOICE' AS CHAR(7)), INVOICEDATE,
AMOUNT
FROM INVOICES
UNION
SELECT ID, CUSTOMERID, CAST('PAYMENT' AS CHAR(7)), PAYDATE, -AMOUNT
FROM PAYMENTS
),

CTE_A(ID, CUSTOMERID, WHAT, IPDATE, AMOUNT, BALANCE)
AS (SELECT UT.ID, UT.CUSTOMERID, UT.WHAT, UT.IPDATE, UT.AMOUNT, UT.AMOUNT
FROM UNIONED_TABLES UT
WHERE NOT EXISTS(
SELECT * FROM UNIONED_TABLES UT2
WHERE UT.CUSTOMERID = UT2.CUSTOMERID
AND (UT.IPDATE > UT2.IPDATE
OR (UT.IPDATE = UT2.IPDATE
AND UT.ID > UT2.ID)))
UNION ALL
SELECT UT3.ID, UT3.CUSTOMERID, UT3.WHAT, UT3.IPDATE, UT3.AMOUNT,
A.BALANCE + UT3.AMOUNT
FROM UNIONED_TABLES UT3
JOIN CTE_A A ON UT3.CUSTOMERID = A.CUSTOMERID
AND (UT3.IPDATE > A.IPDATE
OR UT3.IPDATE = A.IPDATE
AND UT3.ID > A.ID)
AND NOT EXISTS(SELECT * FROM UNIONED_TABLES UT4
WHERE UT4.CUSTOMERID = UT3.CUSTOMERID
AND (UT4.IPDATE > A.IPDATE
OR (UT4.IPDATE = A.IPDATE
AND UT4.ID > A.ID))
AND (UT4.IPDATE < UT3.IPDATE
OR (UT4.IPDATE = UT3.IPDATE
AND UT4.ID < UT3.ID))))

SELECT * FROM CTE_A

The idea is to use recursion to get to the next payment or invoice for
the current customer.

My test data seemed to work (and output things in correct order), though
I haven't done any serious testing.

I called this to 'abuse' WITH RECURSIVE. That is because WITH RECURSIVE
supports a limited depth of recursion, and used as WITH RECURSIVE is
intended to be used, people would normally stay below 1024 recursions.
However, I'd say more than 1024 invoices/payments for one customer
doesn't sound like something unthinkable, so you're more likely to run
into this limit than when WITH RECURSIVE is used as it normally would be
used.

Though the above SELECT confirms my hunch that WITH RECURSIVE is an
alternative to a stored procedure if you want a fleeting balance in your
query. My first attempt was rather clumsy, but using two WITH statements
reduced the complexity a lot. I haven't a clue about the performance.

Thanks for providing me with an interesting puzzle for this Friday evening!

HTH,
Set

Sergio H. Gonzalez wrote:

> Hi, I think this is a very old question, but I never did it with Firebird
> before.
>
> I want to do a simpe current acount with in-out-balance columns. The movements
> would come from the invoices and payments tables ordered by date. I usually did
> it with triggers that insert the movements in a separate table and re-calculate
> all the balances (with a stored procedure) for each record every time something
> in inserted, modified or deleted. I wonder if there is a way to do it "on the
> fly".. I mean when the user want to see some current account, just do " select
> (some fields) from invoices union all select (some fields) from payments order
> by 1" (being "1" the pos of the date field). Firts of all, I don't know how fast
> would be this approach as the DB gets bigger, and second: I dont know how to
> resolve the "balance" column on a select, because it depends on the previous
> row. May I have some diferent opinions of experienced people? Thanks!! -s