--- In firebird-support@yahoogroups.com, "zaher dirkey"
<zaherdirkey@...> wrote:

>
> There is another case same with Record number, it is "Sum Changes"
>
> Date Amount
> 1/1/2006 1000
> 1/2/2006 500
> 1/3/2006 1000
>
> we need results as
>
> Date Amount Current Balance
> 1/1/2006 1000 1000
> 1/2/2006 500 1500
> 1/3/2006 1000 2500

I don't see the relevance of record number in this query? All you need
to do is carry over the current balance from the prior record, not the
sequence number. With FB2, this could probably be achieved in a
similar way suggested by Ivan for the other problem (using context
variables).

With FB1.5, it is trivial to write a stored procedure which returns this

CREATE OR ALTER PROCEDURE FOO
RETURNS
(
ADate DATE,
Amount BIGINT,
CurrentBalance BIGINT
)
AS
BEGIN
CURRENTBALANCE = 0;

FOR SELECT ADATE, AMOUNT FROM BAR INTO :ADATE, :AMOUNT
DO
BEGIN
CURRENTBALANCE = :CURRENTBALANCE + :AMOUNT;
SUSPEND;
END
END
^

Then you can just run

SELECT * FROM FOO;

(Note also that Date is a reserved word)

>
> I know it is must in the client side, but always my friends ask me how
> to make it in SELECT query.
>
> I think it is similar to your state but with another target, in Record
> number we need Sum 1 for every fetch, here we need Sum a field value
> for each fetch.
>

Of course by this information can be very easily computed at the
application end, so the argument over whether you want to 'bloat' the
information as it is transferred from database server to application
server is valid here too.