Query

A query is an object-oriented interface to the SQL database. A query is composed
of objects from different classes.
Various objects are available to manipulate a query object from the AOT.
Use the queryRun object to execute the query and fetch data.
The query object is the definition master. It has its own properties and has one or
more related data sources.
The queryBuildDataSource defines access to a single table in the query. If one
data source is added below another data source, they form a join between the two
tables.
The queryBuildFieldList object defines which fields to fetch from the database.
The default is a dynamic field list that is equal to a "select * from …". Each data
source has only one queryBuildFieldList object which contains information
about all selected fields. You can also specify aggregate functions like sum,count, and avg with the field list object.
The queryBuildRange object contains a limitation of the query on a single field.
The queryFilter object is used to filter the result set of an outer join. It filters the
data at a later stage than the queryBuildRange object and filters the parent table
based on the child table results.
The queryBuildDynaLink objects can only exist on the outer data source of a
query. The objects contain information about a relation to an external record.
When the query is run, this information is converted to additional entries in the
"where" section of the SQL statement. The function is used by forms when two
data sources are synchronized. The subordinate data source contains DynaLink(s)
to the master data source. The function is used even if the two data sources are
placed in two different forms, but are synchronized.
The queryBuildLink objects can only exist on inner data sources. The objects
specify the relation between the two tables in the join.
The following is a sample of code using some of these QueryBuild objects, to
build a query and loop through it:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

Query q; QueryRun qr; QueryBuildDatasource qbds1, qbds2; QueryBuildRange qbr; q = new Query(); qbds1 = q.addDataSource(tablenum(InventTable)); qbds2 = qbds1.addDataSource(tablenum(InventTrans)); qbds2.relations(TRUE); //this enforces a relationship between this datasource and its parent. Relationships defined in the Data Dictionary are used by default. qbr = qbds1.addRange(fieldnum(InventTable, ItemId)); qbr.value(SysQuery::value("1706")); //SysQuery object provides various static methods to assist in defining Query criteria. The SysQuery::value() method should always be used when defining a singular value for a range. qr = new QueryRun(q); while(qr.next()) { //do something }

You can build a query in the AOT using MorphX or as shown in the previous
topic, by dynamically creating the query by X++ code. Both approaches are used
in the standard application. One advantage of making the query dynamic is that it
is not public in the AOT and is protected against unintentional AOT changes.
Alternatively, an advantage of creating the query in the AOT is that it can be
reused in various places, saving lines of identical code, and making widereaching query adjustment easier.

Forms and reports use queries for fetching data. These queries are not public
below the Query-node in the AOT. Forms generate the queries dynamically from
the setting of the data source, whereas Reports have an integrated query-node. In
both cases, you can edit the query for modification.
There are alternative ways of specifying existing objects in a query. Best practice
is to use the intrinsic functions which use table and field ID as arguments
(tableNum and fieldNum).

Best Regards,
Hossein Karimi