Reports have always been available in NAV as a data retrieval tool. Reports are used to process and/or manipulate the data through the Insert, Modify, or Delete functions with the option of presenting the data in a formatted, printable format. Prior to NAV 2013, data selection could only be done using C/AL code or DataItem properties to filter individual tables as datasets, and to perform loops to find the data required for the purpose. Then the Query object was created with performance in mind. Instead of multiple calls to SQL to retrieve multiple datasets to then be manipulated in C/AL, Queries allow us to utilize familiar NAV tools to create advanced T-SQL queries.
An NAV developer can utilize the new Query object as a source of data both in NAV and externally. Some of the external uses of NAV Queries are as follows:
- A web service source for SOAP, OData, and OData
- Feeding data to external reporting tools, such as Excel, SharePoint, and SSRS
Internally, NAV Queries can be used as follows:
- A direct data source for Charts.
- As providers of data to which Cues (displayed in Role Centers) are bound. See the Help Walkthrough: Creating a Cue Based on a Normal Field and a Query.
- As a dataset variable in C/AL to be accessed by other object types (Reports, Pages, Codeunits, and so on). See https://msdn.microsoft.com/en-us/dynamics-nav/read-function--query- for guidance on using the READ function to consume data from a Query.
Query objects are more limited than SQL stored procedures. Queries are more similar to an SQL View. Some compromises in the design of Query functionality were made for better performance. Data manipulation is not supported in Queries. Variables, subqueries, and dynamic elements, such as building a query based on selective criteria, are not allowed within the Query object.
The closest SQL Server objects that Queries resemble are SQL Views. One of the new features that allow NAV to generate advanced T-SQL statements is the use of SQL Joins. These include the following Join methods for two tables, A and B:
- Inner: This query compares each row of table A with each row of table B to find all the pairs of rows that satisfy the Join criteria.
- Full Outer: This join does not require each record in the two Joined tables to have a matching record so that all records from both A and B will appear at least once.
- Left Outer Join: In this join, every record from A will appear at least once, even if matching B is not found.
- Right Outer Join: In this join, every record from B will appear at least once, even if matching A is not found.
- Cross Join: This join returns the Cartesian product of the sets of rows from A and B. The Cartesian product is a set made up of rows that include the columns of each row in A along with the columns of each row in B for number of rows; in other words, including the columns of the rows in A plus those in B.