Beispiel für einen Query mit Gruppierung

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