Query with Multiple range

Finance and Operations/AX Developer Forum
1

Hi,
I wanted to set range for a field in the dynamic query, where that field is summed using addselectionfield in the query, Can we assign a range for the field result which is added up using selectionField:: sum using query , if possible how to add a range for that field in Ax 2009.

Thanks in Advance,
Gopinath M

2

Can you elaborate this? If possible show us the code.

3

If you want to filter records based on the result of the aggregation function (SUM), it’s not a usual range. SQL uses HAVING for this purpose, which unfortunately isn’t supported by AX 2009. You would have to use a workaround, such as putting the result to a temporary table and filter records fetched from this temporary table.

Note that my understanding of your problem doesn’t match the title of your question, as it doesn’t involve any “Query with Multiple range”. Either I got it wrong, or you’re using a wrong title.

4

I have a query like,
query = new Query();
datasource = query.addDataSource(tableNum(CustInvoiceJour));
datasource.addGroupByField(fieldNum(CustInvoiceJour,OrderAccount));
query.dataSourceTable(tableNum(CustInvoiceJour)).orderMode(OrderMode::GroupBy); query.dataSourceTable(tablenum(CustInvoiceJour)).addSelectionField(fieldnum(CustInvoiceJour,InvoiceAmount),SelectionField::Sum);
for this amount field if the resultant added value is greater than a value say 20000 I should print that in info , this should be achieved using query in ax 2009

5

If so, i think Marin has already answered you.