Dynamics AX Blog - Dynamics AX 2009 - Beiträge vom Februar 2011

Der nachstehende Code zeigt wie man einen Query mit Gruppierung, Sortierung und verschiedenen Filtern aufbaut und diesen mit Hilfe eines QueryRun abarbeitet.

static void tutorialQueryGroupBy(Args _args)
{
    Query                query;
    queryBuildDataSource queryBuildDataSource;
    queryBuildRange      queryBuildRangeInvoiceDate;
    queryrun             queryrun;
    Salesline            Salesline;
    ;

    query = new query();

    queryBuildDataSource = query.addDataSource(tableNum(Salesline));

    // Add group by-clause
    queryBuildDataSource.addGroupByField(fieldNum(Salesline, custAccount));
    queryBuildDataSource.addGroupByField(fieldNum(Salesline, currencyCode));

    // Add fields to select-clause
    queryBuildDataSource.addSelectionField(fieldNum(Salesline, custAccount),        SelectionField::Database);
    queryBuildDataSource.addSelectionField(fieldNum(Salesline, currencyCode),       SelectionField::Database);
    queryBuildDataSource.addSelectionField(fieldNum(Salesline, lineAmount),         SelectionField::Sum);
    queryBuildDataSource.addSelectionField(fieldNum(Salesline, createdDateTime),    SelectionField::Min);

    // Add sort by-clause
    queryBuildDataSource.addSortField(fieldNum(Salesline, custAccount), SortOrder::Ascending);

    // Add where-clause
    sysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(Salesline, DeliveryCountryRegionId)).value("AT, US");                                            
    sysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(Salesline, createdDateTime)).value(SysQueryRangeUtil::lessThanDate(-365));                       
    sysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(Salesline, salesStatus)).value(strFmt("%1, %2", SalesStatus::Invoiced, SalesStatus::Delivered));

    // Represents Select
    // SELECT FIRSTFAST CustAccount, CurrencyCode, SUM(LineAmount), MIN(createdDateTime)
    // FROM SalesLine
    // GROUP BY SalesLine.CustAccount, SalesLine.CurrencyCode
    // ORDER BY SalesLine.CustAccount ASC
    // WHERE ((DeliveryCountryRegionId = N'AT' OR DeliveryCountryRegionId = N'US'))
    //   AND ((createdDateTime<'2009-07-06T22:00:00'))
    //   AND ((SalesStatus = 3 OR SalesStatus = 2))


    queryrun = new SysQueryRun(query);
    while(queryRun.next())
    {
        Salesline = queryRun.get(tableNum(SalesLine));

        info(strFmt("%1: %2 %3 (%4)", salesLine.CustAccount, 
                                      salesLine.LineAmount, 
                                      salesLine.CurrencyCode, 
                                      salesLine.createdDateTime));
    }
}