Query objects in AL: when a query beats a loop, and how to write one

Most AL developers learned to get data by looping: FindSet over one table, Get from another inside the loop, add things up in variables. It works, and on a busy table it is the slowest thing in the extension. A query object does the same work as one SQL statement, with joins and aggregation done by the database, and it is the right tool for most reporting, analysis and "give me totals by X" code.

What a query is

A query declares data items (tables) linked by DataItemLink, the columns to return, filters, and optionally aggregation (Method = Sum | Count | Average | Min | Max) with grouping implied by the non-aggregated columns. It compiles to a single T-SQL statement with joins. It is read-only.

query 50120 "CON Sales By Item Month"
{
    QueryType = Normal;
    elements
    {
        dataitem(SalesInvoiceLine; "Sales Invoice Line")
        {
            DataItemTableFilter = Type = const(Item);
            column(ItemNo; "No.") { }
            column(PostingDate; "Posting Date") { }
            column(Amount; Amount) { Method = Sum; }
            column(Quantity; Quantity) { Method = Sum; }
            dataitem(Item; Item)
            {
                DataItemLink = "No." = SalesInvoiceLine."No.";
                SqlJoinType = InnerJoin;
                column(ItemCategory; "Item Category Code") { }
            }
        }
    }
}

Reading it from code

procedure TotalsForItem(ItemNo: Code[20]; FromDate: Date; ToDate: Date): Decimal
var
    SalesByItem: Query "CON Sales By Item Month";
    Total: Decimal;
begin
    SalesByItem.SetRange(ItemNo, ItemNo);
    SalesByItem.SetRange(PostingDate, FromDate, ToDate);
    SalesByItem.Open();
    while SalesByItem.Read() do
        Total += SalesByItem.Amount;
    SalesByItem.Close();
    exit(Total);
end;

Filters set with SetRange/SetFilter on columns become WHERE clauses. TopNumberOfRows and OrderBy control size and order. A query can be the source of a report (dataset from a query) and of a page (SourceTable as a temporary record you fill from the query), and with QueryType = API it is exposed as a read-only web service, which is how you give Power BI or a web shop stock by location in one call.

When to use one

• Totals, counts or averages by some grouping.
• Anything that joins two or more tables and reads many rows.
• Report datasets that are currently nested data items with filtering in triggers.
• Analytics endpoints for Power BI or integrations.

Not for single-record lookups, not for writes, and not where you need the record's triggers, FlowFields or permissions semantics of a page. Queries respect table permissions but not security filters in the same way pages do; check with your permission model. For date grouping, use a DateMethod (Day, Month, Year) on a date column rather than grouping on the raw date. And remember a query returns what SQL returns: if a filter column has no index, the query is a scan, so keys still matter (keys post).

Related posts