Problem with SUM aggregate function

Finance and Operations/AX Developer Forum
1

Hi all!

We have a problem with Sum aggregate function.

For example:

The value in the table is: 123,45

It returns: 1234,5

Someone have the same problem?

Thanks for the replies.

2

It’s much more likely that the problem is in your code than that you’ve just found a serious bug in AX or SQL Server.

Can you show us what exactly you did?

3

This is my code:

query = new Query();

qbds = query.addDataSource(tableNum(ProjInvoiceJour));

qbds.addSelectionField(fieldNum(ProjInvoiceJour, InvoiceAmount), SelectionField::Sum);

qbds = qbds.addDataSource(tableNum(ProjProposalJour));

qbds.joinMode(JoinMode::InnerJoin);

qbds.addLink(fieldNum(ProjInvoiceJour, ProjInvoiceProjId),fieldNum(ProjProposalJour, ProjInvoiceProjId));

qbds = qbds.addDataSource(tableNum(ProjProposalEmpl));

qbds.joinMode(JoinMode::InnerJoin);

qbds.addLink(fieldNum(ProjProposalJour, ProposalId),fieldNum(ProjProposalEmpl, ProposalId));

qbds = qbds.addDataSource(tableNum(ProjEmplTrans));

qbds.joinMode(JoinMode::InnerJoin);

qbds.addLink(fieldNum(ProjProposalEmpl, TransId),fieldNum(ProjEmplTrans, TransId));

qbds.addSelectionField(fieldNum(ProjEmplTrans, ProjId));

qbr = qbds.addRange(fieldNum(ProjEmplTrans, ProjId));

qbr.value(projId);

queryRun = new QueryRun(query);

while (queryRun.next())

{

//info(queryRun

jour = queryRun.get(tableNum(ProjInvoiceJour));

print jour.InvoiceAmount;

}

pause;

4

This is query query string (got by calling query.dataSourceNo(1).toString()):

SELECT SUM(InvoiceAmount) FROM ProjInvoiceJour(ProjInvoiceJour_1)

JOIN * FROM ProjProposalJour(ProjProposalJour_1)
ON ProjInvoiceJour.ProjInvoiceProjId = ProjProposalJour.ProjInvoiceProjId

JOIN * FROM ProjProposalEmpl(ProjProposalEmpl_1)
ON ProjProposalJour.ProposalId = ProjProposalEmpl.ProposalId

JOIN ProjId FROM ProjEmplTrans(ProjEmplTrans_1)
ON ProjProposalEmpl.TransId = ProjEmplTrans.TransId
AND ((ProjId = N'Some project ID'))

Is it what you want? Didn’t you want, for example, the filter ProjInvoiceJour by project ID?

Unfortunately I don’t know data in your database, so I can’t tell what the query returns.

5

I want to sum all the invoice amount for each project.

6

That’s not what your query does, it take into account only invoices created from proposal, with at least one employee transaction and just for a single project.