如何使用查询选择聚合字段？

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

我正在尝试使用addselectionfield选择4个字段。来自那4个字段2的是聚合（和）字段。

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:

在调试时，我在queryRun中得到以下select语句：

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?

那么它只选择最后一个addSelectionField以及我必须做什么来获取我在addSelectionFields中编写的所有字段的原因是什么？

1 个解决方案

#1

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.

没有理由（我能想到）......这应该有效，应该将选择域添加到querybuilddatasource中。尝试同步，编译和/或重新启动客户端/ 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:

您还必须做的是在要选择的字段上添加分组字段。否则AX将不知道选择什么值，因为多个记录可以具有不同的值。例如，要选择CurrencyNameField，请将其添加为选择字段，并使用：

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

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

然后，您将获得CurrencyNameField字段中的数据，但您将获得每个货币名称的总和。

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:

queryrun包含：

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

#1