How to select aggregate fields using query?

I'm trying to select 4 field with addselectionfield. from those 4 field 2 are aggregate (sum) fields.

I'm doing the following:

            date fromDate = systemDateGet();
            date toDate = (systemDateGet() + 25);
            query          = new Query();
            qbds           = query.addDataSource(tableNum(MyTable));
            qbds.addSelectionField(fieldNum(MyTable, DateField));
            qbds.addSelectionField(fieldNum(MyTable, USDAmountField), SelectionField::Sum);
            qbds.addSelectionField(fieldNum(MyTable, CurrencyNameField));
            qbds.addSelectionField(fieldNum(MyTable, EURAmountField), SelectionField::Sum);
            qbr            = qbds.addRange(fieldNum(MyTable, DateField));
            qbr.value(queryRange(fromDate, toDate));

            queryRun       = new QueryRun(query);

While debugging I get following select statement in queryRun:

SELECT SUM(EURAmountField) FROM MyTable(MyTable_1)
WHERE ((DateField >= {ts '2014-04-01 00:00:00.000'}
AND DateField <= {ts'2014-04-26 00:00:00.000'}))

So what is the reason that it selects only the last addSelectionField and what I have to do to get all fields as I have written in the addSelectionFields?

Answers


There is no reason (I can think of)... this should work, the selectionfield should be added to your querybuilddatasource. Try synchronizing, compiling and/or restarting your client/aos.

What you have to do too though is add group by fields on the field that you want to select. Otherwise AX will not know what value to select because multiple records can have a different value. For example, to select the CurrencyNameField, add it as a selection field and also use:

qbds.addGroupByField(fieldNum(MyTable, CurrencyNameField)); 

Then you will get data in the CurrencyNameField field but you will get the sum for each currency name.

This seems to work:

Query query;
QueryBuildDataSource qbds;
QueryBuildRange qbr;
QueryRun queryrun;

date fromDate = systemDateGet();
date toDate = (systemDateGet() + 25);
query          = new Query();
qbds           = query.addDataSource(tableNum(MyTable));
qbds.addGroupByField(fieldNum(MyTable, DateField));
qbds.addGroupByField(fieldNum(MyTable, CurrencyNameField));
qbds.addSelectionField(fieldNum(MyTable, DateField));
qbds.addSelectionField(fieldNum(MyTable, CurrencyNameField));
qbds.addSelectionField(fieldNum(MyTable, EURAmountField), SelectionField::Sum);
qbds.addSelectionField(fieldNum(MyTable, USDAmountField), SelectionField::Sum);
qbr            = qbds.addRange(fieldNum(MyTable, DateField));
qbr.value(queryRange(fromDate, toDate));

queryRun       = new QueryRun(query);

The queryrun contains this:

SELECT DateField, CurrencyNameField, SUM(EURAmountField), SUM(USDAmountField) 
FROM MyTable(MyTable_1) GROUP BY MyTable.DateField, MyTable.CurrencyNameField 
WHERE ((DateField>={ts '2014-04-01 00:00:00.000'} 
AND DateField<={ts '2014-04-26 00:00:00.000'}))

Need Your Help

What do you use for running scheduled tasks in Production for Python?

python scheduled-tasks scheduling production dev-to-production

The thing is, I read this post stating best practices to set up a code to run at every specified interval over a period of time using the python library - APS Scheduler. Now, it obviously works per...