>A is invoice header
>B is invoice rows
>
>SO I need to
>- aggregate rows on the same B.cms_code (something like pn type)
>- summing B.pn_value
>- filering on A.invoice_Date
>
>With your code something strange happens.... I have value more greater than invoice total.
>

Marks code:

>SELECT SUM(b3), b2
>FROM A
>INNER JOIN B ON B.b1=A.a1
>WHERE a2 = ? -- Your filter condition
>GROUP BY b2

Hi Fabio!

Probably not strange at all, if the sum is greater than the total, that indicates that A.A1 isn't unique, and if you join to A1, then the SUM would be the sum you expect multiplied with the number of duplicate A1s.

So, do a slight modification:

WITH Tmp as
(SELECT DISTINCT A.A1
WHERE A.invoice_Date = :invoice_date
FROM A)

SELECT SUM(B.pn_value), B.cms_code
FROM Tmp
JOIN B ON B.b1=Tmp.a1 /* We don't know which fields to join */
GROUP BY b.cms_code

If this still isn't what you're looking for, well, then try to explain your problem once more, remembering that we know nothing about your situation, so make sure to tell what fields we can expect to be unique, what it is that you're trying to obtain and anything else that is of importance (I for one, have no clue what 'pn type' means or whether it matters that I'm ignorant about that).

HTH,
Set