|Subject||Re: [firebird-support] Re: How write a query with a progressive sum field|
Il 08/03/2016 10.05, rik@... [firebird-support] ha scritto:
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 :(
I get exception: "invalid expression in the select list (not contained in either an aggregate function or the group by clause)".
You could try the following:select
(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 SALDOfrom DOC_TESTA DT
JOIN DOC_CORPO DC on DC.DOC_TESTA_ID = DT.IDWHERE DC.ARTICOLO_ID = :ID
GROUP BY DATA, DOCUMENTO_ID, NUMERO, SERIE
ORDER BY DATA, DOCUMENTO_ID, NUMERO, SERIE
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.
Luigi Siciliano --------------------------