How to select aggregate fields using query?

Question

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?

Klaas Deforche Klaas Deforche · Accepted Answer · 2014-04-01T13:19:31

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'}))

How to select aggregate fields using query?

1 Answers