I'm looking at your statement and my first instinct was that it should be correct (also for CARICO = 0 and SCARICO = 0). But you need to make sure that they are really filled with 0 and not NULL.

Yes, they are ever NOT NULL because I provide default value = 0. I verified :)

Furthermore... You do a LEFT JOIN with DC2 on DOC_CORPO but there is no ORDER BY for the DC2. Doesn't that mean that DC2 is un-ordered (ordering can be anything). Using the SUM() in that case seems dangerous because now you assume the ordering of DC2 is the same as DC.

The query seems to works ok but is not ever true because it could be that I have a DC.DOC_TESTA_ID not in same order by DATA + DOCUMENTO_ID + NUMERO + SERIE and in this case the result of SALDO field is not correct :(

You could try the following:

select
  DT.DATA,
  DT.DOCUMENTO_ID,
  DT.NUMERO,
  DT.SERIE,
  DC.CARICO,
  DC.SCARICO,
  (SELECT SUM(DC2.CARICO - DC2.SCARICO) FROM DOC_CORPO DC2

   WHERE (DC2.DOC_TESTA_ID = DT.ID) AND (DC2.ARTICOLO_ID = DC.ARTICOLO_ID)

   AND (DC2.DOC_TESTA_ID <= DC.DOC_TESTA_ID) ) AS SALDO

from DOC_TESTA DT

JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.ID

WHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE

I get exception: "invalid expression in the select list (not contained in either an aggregate function or the group by clause)".
I don't understand the GROUP BY clause who I need to write in sub-select because if I put the same as in select: "GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE" I get the exception: "cannot use an aggregate function in a GROUP BY clause."
I'm confused :(

(I'm not sure if you should add DC.DOC_TESTA_ID to the order clause. If that works you should also do an ORDER BY in the sub-select)

If I understand correctly, not works.

Thanks

--

Luigi Siciliano
--------------------------