Walkthrough: Creating an AOT Query that has Group By and Having Nodes
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
In this walkthrough you create Group By and Having nodes under a query in the Application Object Tree (AOT).
The query you create shows the number of customer records for each destination code where there are more than six customers and the destination code is not equal to Gen_5. If the query you create was represented in standard ANSI SQL, the select query would look like the following:
select
ct.DestinationCodeId
,count(*)
from
CustTable as ct
where
ct.DestinationCodeId != 'Gen_5'
group by
ct.DestinationCodeId
having
count(*) > 6
order by
1;
Note
In the example of this topic the query has its AllowCrossCompany property set to Yes. This setting means the query does no filtering on company or party.
Create the Query Entry
You create the query in the AOT by the following steps:
Expand AOT > Queries.
Right-click the Queries node, and then click New Query.
In the Properties window, edit the Name property value to be QryGbyHavgRange23.
Set the AllowCrossCompany property to Yes.
Right-click your QryGbyHavgRange23 node, and then click Save to confirm the node and property changes.
Expand the node for your new query, so that you can see its subnodes including its Data Sources subnode.
Proceed to create the subnodes that are described in the table that follows.
Add a Data Source
Next you must add a data source node to your query.
Subnode |
Steps |
Properties window |
---|---|---|
Data Sources |
Add the CustTable as a data source.
This query reads data from only one table, the CustTable table. |
Add Subnodes Under Data Sources
The following table shows the steps to create each subnode under
AOT > Queries > QryGbyHavgRange23 > Data Sources. The table also shows the Properties window for each subnode.
Subnode |
Steps |
Properties window |
---|---|---|
Fields |
Add a field under the new CustTable data source node.
ANSI SQL typically uses an asterisk for the count aggregate function. But in Microsoft Dynamics AX a field must be used, and by convention the RecId field is usually used. |
|
Ranges |
Add a range node to exclude one particular value of the DestinationCodeId field. Each range node applies to the Where clause of the SQL Select statement that is eventually generated.
Notice that the Value property is set to both a comparison operator and a specific data value. Inclusion of an operator is optional. |
|
Group By |
Add a Group By clause. A query cannot have a Having clause unless it also has a Group By clause. For each unique value in the DestinationCodeId field, the query counts all the CustTable records that share the same DestinationCodeId value with each other. This is accomplished by adding a group by clause.
Implicitly the system adds the group by field to the fields list at run time, so that the group by field is also returned when the query is run. |
|
Having |
Add a Having clause to filter the aggregate values that are generated by the Group By clause. In the present example, the COUNT(RecId) field contains the aggregate values that are filtered.
The value filter is compared against the COUNT aggregate result of each record that otherwise can be returned. The value is not compared against the RecId field. |
|
Order By |
Add an Order By node. The order by clause operates on the records that remain after all the filtering is completed.
|
Next is an image of how the finished query looks when it is fully expanded in the AOT:
Note
The preceding screen images were taken from an installation of Microsoft Dynamics AX 2012.
X++ Code to Run the Query
You can use the following X++ code to run the AOT query created in the previous procedure. The code is an X++ job that you can paste into a new job under AOT > Jobs.
static void QryGbyHavgRange23Job(Args _args) // X++ job.
{
CustTable ct;
Query q = new Query(queryStr(QryGbyHavgRange23));
QueryRun qr = new QueryRun(q);
while(qr.next())
{
ct = qr.get(tableNum(CustTable), 1);
info(strFmt("(Q23) , %1 , %2",
ct.DestinationCodeId, ct.RecId));
}
info("Done.");
}
/*** Infolog display of results:
Message (04:11:40 pm)
(Q23) , Gen_9 , 8
(Q23) , Gen_8 , 11
(Q23) , Gen_7 , 10
(Q23) , Gen_4 , 15
(Q23) , Gen_3 , 8
(Q23) , Gen_2 , 21
(Q23) , , 26
Done.
***/
See also
Queries in the AOT for Data Access
Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.