Advanced query concepts
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