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 aren't the same. However, they achieve the same goal. The following list shows a 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're 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
Query discoverability
By setting the UsageCategory property, you can make queries discoverable for users in Tell Me search and in the role explorer under Report and Analysis.
You can also provide teaching tips by setting the AboutTitle Property and AboutText Property, and provide dedicated help links on the query object to help users understand how to use the query by setting the ContextSensitiveHelpPage Property.
View and analyze query data
You can view and analyze query data directly from the Business Central client by running the query, and then switching to it in analysis mode. The data is analyzed in real time and respects the data security that has been set up for the users.
There are a couple ways to run a query. One way to run a query is to use
the query=<id> in the web client URL. For example, this URL opens the
query that has the ID 102:
https://businesscentral.dynamics.com/?company=CRONUS%20USA%2C%20Inc.&query=102```
Another way to run the query is to add an action on a page and use the
[RunObject Property](/dynamics365/business-central/dev-itpro/developer/properties/devenv-runobject-property?azure-portal=true). The following example shows
how to add an action that opens a query on the Item l List page:
```al-languageCopy
pageextension 50110 ItemListWithQuery extends "Item List"
{
actions
{
addbefore("Inventory - List")
{
action("Analyze customers")
{
ApplicationArea = All;
Caption = 'Analyze customers';
RunObject = query "Top Customer Overview";
Tooltip = 'Open the customer query in analysis mode.';
Image = Item;
}
}
}
}
Once the query opens in the client, turn on the Analyze switch to change to analysis mode.
For more information, see Analyze list page and query data using data analysis mode.