sum function in X++

[Marien]

Hello everyone!
Does enyone know if there is more elegant way to achieve SQL query below in
X++ then using the while loop?

select sum(Qty * amount) from OrderItem
where OrderItem.OrderNo = 'abc'

Thanks

[Willy]

Hi,

Take the following as an example:

server static void TestSumSelect()
{ Connection connection;
Statement statement;
str sql;
ResultSet resultSet;
SqlStatementExecutePermission perm;
;

connection = new Connection();
sql = strfmt( "SELECT SUM(QtyOrdered * SalesPrice) FROM SALESLINE where
SalesId='xyz'");
perm = new SqlStatementExecutePermission(sql);

perm.assert();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);

while(resultSet.next())
{
info(num2str(resultSet.getReal(1),0,2,1,0));
}

CodeAccessPermission::revertAssert();
}

Code needs to run on server, put it in a class somewhere. (With args)
Good luck,

Willy (blog at http://dynamics-ax-live.blogspot.com)

[Luegisdorf]

select sum(Qty), sum(amount) from OrderItem
where OrderItem.OrderNo = 'abc'

x = orderItem.Qty * orderItem.Amount

:)

[Alex K. at dot]

This will not work:

> x = orderItem.Qty * orderItem.Amount

For example, if you have OrderItem like this:
Qty Amount
1 100
2 10

Then 3*(110) != (100 + 2*10).
Okey, lets say that smile at the end of the message was placed there with
purpose ;)

I think in this case is easier to introduce new table field, which should be
filled with (Qty*Amount) on insertion, rather than use direct SQL statements.
Otherwise use while loop.

--
Regards,
Alex
----
Microsoft Dynamics AX Add-on for developers(Imptoved IntellySense,
additional hotkeys etc) -http://www.axassist.com

[Marien]

Thank you all!
I needed this for a display method of datasource on a form, so I used
while loop and cashed this method. It works fine enough:)

[Pranjal Shukla]

you can try this display method::
https://d365code.blogspot.com/2021/03/return-sum-of-any-table-field-using-x.html