How to perform arithmetic operations inside agreegations in x++ query?

Finance and Operations/AX Developer Forum
1

Hi,

What is the equivalent x++ query for the below SQL query.

Select sum(SalesQty/LineAmount) as unitprice from salesline where salesline.SalesID=‘SO00001’
group by SalesId;

Thanks in advance

Vasanth S

2

HI Vasanth,

Using a calculation directly in x++ is not possible. You can try to create a view with computed columns (T-SQL).

3

static void Job5(Args _args)
{
SalesLine salesline;
select sum(LineAmount) from salesline group by salesline.CustAccount where salesline.SalesId == ‘MoS-00503’;
info(strFmt(’%1’,salesline.LineAmount));
}

4

Kunal, can you explain how your reply addresses the problem? It doesn’t seem to be the case…

5

yes martin you are right i miss the arithmetic function , can you please help to edit my reply?

6

Hello Vasanth ,

please ignore my last comment as i have missed your arithmetic expression , as Andre suggests you cant directly use aggregate function as this and you can go with view .

if you want you can go with below query if it fits in your case

static void Job5(Args _args)
{
SalesLine salesline;
real total;
total = 0;
while select LineAmount,SalesQty from salesline where salesline.SalesId == ‘MoS-00503’
{
total = total + (salesline.LineAmount/salesline.SalesQty);
}
info(strFmt(’%1’,total));
}

7

Thanks all

8

So… do you need any help regarding computed columns? Unfortunately you forgot to attach a tag with your version of AX, so we don’t know whether your version support it.

If your has been answered, please close this thread by verifying answers.