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.
