Den 07.03.2016 11:16, skrev Luigi Siciliano luigisic@...
[firebird-support]:

> Hallo,
> I need to write a query with a computed field that contain a
> progressive sum like this table:
>
> DATA DOCUMENTO_ID NUMERO SERIE CARICO SCARICO SALDO
> 01/01 A 1 A
> 1 0 1
> 02/01 A 2 A
> 3 0 4
> 02/01 V 33 B
> 0 2 2
> 02/01 V 35 C
> 0 1 1
>
> I need to modify this query by adding SALDO field to get progressive sum
> of CARICO - SCARICO:
> select
> DT.DATA,
> DT.DOCUMENTO_ID,
> DT.NUMERO,
> DT.SERIE,
> DC.CARICO,
> DC.SCARICO
> from
> DOC_TESTA DT
> JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
> WHERE
> DC.ARTICOLO_ID = :ID
> ORDER BY
> DATA,
> DOCUMENTO_ID,
> NUMERO,
> SERIE
>
>
> How I do?
>
> Thanks

Maybe this can be solved like this:

select
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE,
DC.CARICO,
DC.SCARICO,
(SELECT SUM(DC2.CARICO-DC2.SCARICO)
FROM DOC_TESTA DT2
JOIN DOC_CORPO DC2 on DT2.ID = DC2.DOC_TESTA_ID
WHERE DC.ARTICOLO_ID = DC2.ARTICOLO_ID
AND (DT.DATA > DT2.DATA
OR (D2.DATA = DT2.DATA
AND (DT.DOCUMENTO_ID > DT2.DOCUMENTO_ID
OR (DT.DOCUMENTO_ID = DT2.DOCUMENTO_ID
AND (DT.NUMERO > DT2.NUMERO
OR (DT.NUMERO = DT2.NUMERO
AND DT.SERIE >= DT2.SERIE))))))) SALDO
from
DOC_TESTA DT
JOIN DOC_CORPO DC on DT.ID = DC.DOC_TESTA_ID
WHERE
DC.ARTICOLO_ID = :ID
ORDER BY
DT.DATA,
DT.DOCUMENTO_ID,
DT.NUMERO,
DT.SERIE

This will not work properly if more than one row of DC are joined to the
same DT (but then your ORDER BY isn't 100% deterministic). If you change
the ordering of your query, you also have to change the subselect (e.g.
if you add DESC you have to change from > to <).

If this doesn't fit, is too slow or too difficult to understand, I'd
recommend EXECUTE BLOCK (as Sean already wrote).

HTH,
Set