Advanced query concepts

Completed

When you use queries, it helps if you understand how accessing queries in AL code relates to accessing tables. Also, it helps if you understand how queries map to Transact-SQL statements.

When you are retrieving data, you can use records and queries and iterate through datasets in a similar manner. The AL functions that you use with records and queries are not the same. However, they achieve the same goal. The following list shows a side-by-side comparison of the AL functions that you can use when you access data with records and queries:

  • Initiate iteration through a dataset: FindSet (Record) -- Open (Query)

  • Retrieve the next record from a dataset: Next (Record) -- Read (Query)

  • Close the dataset: Close (Query)

Retrieving data with records and queries is different. With records, the FindFirst and FindSet functions also retrieve the first record, whereas with queries, the Open function merely translates the query model into a Transact-SQL statement and runs it against SQL Server. With records, after the initial FindFirst or FindSet, the record is already retrieved and you can immediately access the fields. With queries, you must call the Read function one time before you can access the columns. Because of this requirement, you would write the iteration loops differently. With records, you can use the REPEAT..UNTIL statement. With queries, you would use the WHILE..DO statement.

The following code sample shows the comparison between record and query iteration loops.

Record Iteration:

if Rec.FindFirst() then
    repeat
    // Do some processing
    until Rec.Next = 0;

Query Iteration:

if SimpleQuery.Open() then
    while SimpleQuery.Read() do begin
        // Do some processing
    end;

Except for the syntactical differences in how you iterate through datasets with records and queries, functional differences exist between retrieving records by using FindFirst or FindSet and using queries. Because of those differences, you can't directly replace traditional record-based iteration with queries.

Mapping queries to Transact-SQL

When Business Central runs a query, the system first translates it into a Transact-SQL statement and then runs that statement against the underlying SQL Server database. One benefit of queries is that they enable you to model a dataset in a user-friendly way. Queries don't require knowledge of Transact-SQL querying language or expertise in SQL Server.

However, if you are familiar with Transact-SQL, you might want to understand exactly how the query features of Business Central map to Transact-SQL. The following list summarizes the elements and properties of query objects and how they relate to Transact-SQL.

  • SELECT - Item of type Column

  • FROM - Item of type DataItem

  • JOIN type - SqlJoinType query properties

  • ON - DataItemLink data item property

  • WHERE - DataItemTableFilter data item property. ColumnFilter property of columns

  • HAVING - ColumnFilter property of columns and filters when aggregation is used

  • GROUP BY - Automatically active for each column item when aggregation is used

  • ORDER BY - OrderBy query property

  • TOP - TopNumberOfRows query property