Need help in writing SQL select statement on Ax sysquery/datasource query script!

Dear Dynamics Ax Community,

May i have the advice for the ax sysquery / datasource query for below sql statement:

Select ct.accountnum, ct.name, ct.mandatorycreditlimit, ct.creditrating, ct.creditmax, ctrans.accountnum, sum(ctrans.amountmst), ctrans.paymmode

from custtable ct

inner join custtrans ctrans on ctrans.accountnum = ct.accountnum and ctrans.dataareaid = ct.dataareaid

where ctrans.paymmode in (‘10’,‘20’,‘30’) and ct.accountnum in (‘1003’,‘1009’)

group by ct.accountnum, ct.name, ct.mandatorycreditlimit, ct.creditrating, ct.creditmax, ctrans.accountnum, ctrans.paymmode

order by ct.accountnum asc

What exactly do you need? Joins, grouping, ordering and SUM() are supported, IN operator isn’t - you’ll have to use multiple ranges.

Thanks for quick reply Martin.

The main query that i would like to design is purposely to get sum(amountMST) from CustTrans and inner join to CustTable.

And in order to design sum(amountMST), i have to put addSelectionField.

Below is my datasource query:

db1 = qr.query().dataSourceTable(tableNum(CustTable));

db2 = db1.addDataSource(tableNum(CustTrans));

db2.joinMode(JoinMode::InnerJoin);

db2.addGroupByField(fieldNum(CustTrans,AccountNum));

db2.addGroupByField(fieldNum(CustTrans,PaymMode));

db2.addSelectionField(fieldNum(CustTrans,AccountNum));

db2.addSelectionField(fieldNum(CustTrans,PaymMode));

db2.addSelectionField(fieldNum(CustTrans,AmountMST),SelectionField::Sum);

db2.addSelectionField(fieldNum(CustTrans,SettleAmountMST),SelectionField::Sum);

db2.orderMode(OrderMode::GroupBy);

db2.addOrderByField(fieldNum(CustTrans,AccountNum),SortOrder::Ascending);

db2.addOrderByField(fieldNum(CustTrans,PaymMode),SortOrder::Ascending);

link1 = db2.addLink(fieldNum(CustTable,AccountNum),fieldNum(CustTrans,AccountNum));

The problem i am facing is, if i design in this way it throws an error saying: “The specified sequence of Group By and Order By fields is not supported”.

Please kindly advice

Which version of AX do you use? Older versions didn’t support Order By and Group By in the same query. Nevertheless it’s not a problem in your case - simply remove addOrderByField() calls; the sorting should be already defined by addGroupByField().

Hi Martin,

Thanks for your reply previously :). I noticed the error will come out only if i used joinmode::ExistJoin. I changed to JoinMode::InnerJoin and it works like a charms. Thanks again for your prompt responsed :).