Queries

Queries are objects whose purpose is to create extracted sets of data from the NAV database and do so very efficiently. NAV 2017 Queries translate directly into T-SQL query statements and run on the SQL Server side rather than on the NAV Service Tier. A query can extract data from a single table or multiple tables. In the process of extracting data, a query can do different types of join (Inner Join, Outer Join, or Cross Join), can filter, can calculate FlowFields (special NAV calculations that are discussed in detail in Chapter 3, Data Types and Fields), can sort, and can create sums and averages. Queries obey the NAV data structure business logic.

The output of a query can be a CSV file (useful for Excel charts), an XML file (for charts or external applications), or an Odata file for a web service. Queries can be published for web service access similarly to pages and codeunits. The results of a query can also be viewed by pages (as described in Chapter 5, Queries and Reports) and Cues (as described in the Walkthrough: Creating a Cue Based on a Normal Field and a Query documentation section (https://msdn.microsoft.com/en-us/dynamics-nav/walkthrough--creating-a-cue-based-on-a-normal-field-and-a-query)), but are especially powerful when output to charts. With a little creatpity, a query can also be used to feed data to a page or report via use of a temporary table to hold the query results.