Announcements
I am trying to query a view "ChequeStatusView" which has among its columns Document Number and Transaction Currency Amount. My requirement was to retrieve summation of Transaction Currency Amount grouped by Document Number where the summation of Transaction Currency Amount is greater than zero. To use where clause on an aggregate function I had to use Having clause. To use Having clause in X++ I had to use Query object to define my query rather than writing a select statement directly. I had the following code:
ChequeStatusView chequeStatusView; Query query; QueryBuildDataSource ds; QueryRun qr; query = new Query(); ds = query.addDataSource(tableNum(ChequeStatusView));
ds.addSelectionField(fieldNum(ChequeStatusView,TransactionCurrencyAmount),SelectionField::Sum);
ds.addSelectionField(fieldNum(ChequeStatusView,DocumentNumber));
ds.addGroupByField(fieldNum(ChequeStatusView,DocumentNumber));
query.addHavingFilter(ds,fieldStr(ChequeStatusView, TransactionCurrencyAmount),AggregateFunction::Sum).value('>0');
query.allowCrossCompany(true);
qr = new QueryRun(query);
This code was not working as expected as all the records of TransactionCurrencyAmount were always zero and by a simple look at the view this result was incorrect as in reality it should have a value.
I thought that the having clause is not working properly so I searched and I stumbled on this link where a code was mentioned on how to view the generated SQL statement which is sent to SQL Server. So I printed this
info(ds.toString());
This was the generation which I got.
SELECT DocumentNumber FROM ChequeStatusView(ChequeStatusView_1) GROUP BY ChequeStatusView.DocumentNumber HAVING ((SUM(TransactionCurrencyAmount)>0))
The aggregate method Sum on Transaction Currency Amount was not found.
Since my SQL syntax is a little rusty I tried to run this code on sql management studio and it only retrieved Document Number column ( I had to remove this bit (ChequeStatusView_1) ).
Just for the sake of trying i switched the order of my addSelectionField methods and placed the Document Number before the aggregate method I got this SQL statement:
SELECT SUM(TransactionCurrencyAmount) FROM ChequeStatusView(ChequeStatusView_1) GROUP BY ChequeStatusView.DocumentNumber HAVING ((SUM(TransactionCurrencyAmount)>0))
From what I understand a that when we add a normal column the previously added aggregate is removed and vise versa.
Is this a bug or this is the way it should work?
For now I have used a normal select statement with an if condition while i iterate through the results, a very inefficient method as mentioned here.
Please let me know if there is another way to use having clause and if the scenario I mention above is a bug which is fixed in some update.
Note my Ax is R3 with no CUs installed.
*This post is locked for comments
Thanks @Oleg, great explanation, helped me in researching the inventory on-hand form.
Hi Murtaza,
thank you for your feedback.
I tested it once again. It worked. Please find details below.
Test 1. Original code
static void TestQuery(Args _args) { Query q = new Query(); QueryBuildDataSource qbds; QueryRun qr; QueryBuildFieldList qbfl; CustSalesOpenLines custSalesOpenLines; qbds = q.addDataSource(tableNum(CustSalesOpenLines)); qbds.firstOnly(true); qbds.addSelectionField(fieldNum(CustSalesOpenLines, LineAmount), SelectionField::Sum); qbds.addSelectionField(fieldNum(CustSalesOpenLines, SalesId)); qbds.addGroupByField(fieldNum(CustSalesOpenLines, SalesId)); q.addHavingFilter(qbds, fieldStr(CustSalesOpenLines, LineAmount), AggregateFunction::Sum).value('>0'); qr = new QueryRun(q); while(qr.next()) { custSalesOpenLines = qr.get(tableNum(CustSalesOpenLines)); info(strFmt("%1 %2", custSalesOpenLines.SalesId, custSalesOpenLines.LineAmount)); } }
Infolog message:
SO-100972 0.00
T-SQL statement:
SELECT T1.SALESID FROM CUSTSALESOPENLINES T1 WHERE (((((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (PARTITION#2=@P3)) AND (DATAAREAID#2=@P4)) AND (PARTITION#3=@P5)) GROUP BY T1.SALESID HAVING (SUM(LINEAMOUNT)>@P6) ORDER BY T1.SALESID
SUM(LineAmount) is not selected.
Test 2. Solution code
static void TestQuery(Args _args) { Query q = new Query(); QueryBuildDataSource qbds; QueryRun qr; QueryBuildFieldList qbfl; CustSalesOpenLines custSalesOpenLines; qbds = q.addDataSource(tableNum(CustSalesOpenLines)); qbds.firstOnly(true); qbfl = qbds.fields(); qbfl.addField(fieldNum(CustSalesOpenLines, SalesId)); qbfl.addField(fieldNum(CustSalesOpenLines, LineAmount), SelectionField::Sum); qbds.addGroupByField(fieldNum(CustSalesOpenLines, SalesId)); q.addHavingFilter(qbds, fieldStr(CustSalesOpenLines, LineAmount), AggregateFunction::Sum).value('>0'); qr = new QueryRun(q); while(qr.next()) { custSalesOpenLines = qr.get(tableNum(CustSalesOpenLines)); info(strFmt("%1 %2", custSalesOpenLines.SalesId, custSalesOpenLines.LineAmount)); } }
Infolog message:
SO-100972 30,566.00
T-SQL statement:
SELECT SUM(T1.LINEAMOUNT),T1.SALESID FROM CUSTSALESOPENLINES T1 WHERE (((((PARTITION=@P1) AND (DATAAREAID=@P2)) AND (PARTITION#2=@P3)) AND (DATAAREAID#2=@P4)) AND (PARTITION#3=@P5)) GROUP BY T1.SALESID HAVING (SUM(LINEAMOUNT)>@P6) ORDER BY T1.SALESID
SUM(LineAmount) is selected.
Tested in AX 2012 R2.
It worked. Thanks Andrew :D.
Solution of using QueryBuildFieldList did not work. Still the same issue remains.
Hi Murtaza,
It's not a bug. You can simply resolve this by moving 'ds.addGroupByField(fieldNum(ChequeStatusView,DocumentNumber));' ahead of 'ds.addSelectionField(fieldNum(ChequeStatusView,DocumentNumber));'.
Hi Murtaza,
It can be a bug, but there is a workaround.
You can use QueryBuildFieldList instead of ds.addSelectionField():
QueryBuildFieldList qbfl; qbfl = qs.fields(); qbfl.addField(fieldNum(ChequeStatusView,DocumentNumber)); qbfl.addField(fieldNum(ChequeStatusView,TransactionCurrencyAmount), SelectionField::Sum);
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 290,807 Super User 2024 Season 2
Martin Dráb 229,135 Most Valuable Professional
nmaenpaa 101,156