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;

Create the Query Entry

You create the query in the AOT by the following steps:

1. Expand AOT > Queries.

2. Right-click the Queries node, and then click New Query.

3. In the Properties window, edit the Name property value to be QryGbyHavgRange23.

4. Set the AllowCrossCompany property to Yes.

5. Right-click your QryGbyHavgRange23 node, and then click Save to confirm the node and property changes.

6. Expand the node for your new query, so that you can see its subnodes including its Data Sources subnode.

7. 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. Right-click Data Sources, and then click New Data Source. This creates a new node under Data Sources. Click the new data source node to highlight it. In the Properties window, set the Table property to CustTable. This action also changes the value of the Name property. 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. Expand the new data source node. Right-click the Fields node, and then click New > COUNT. In the Properties window for the new field node, set the Field property to RecId. 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. Right-click the Ranges node, and then click New Range. Set the Field property to DestinationCodeId. Set the Value property to != "Gen_5". 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. Right-click the Group By node, and then click New Field. In the Properties window, set the Field property to DestinationCodeId. 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. Right-click the Having node, and then click New > COUNT. In the Properties window, set the Field property to RecId. Set the Value property to > 6. 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. Right-click the Order By node, and then click New Field. In the Properties window, set the Field property to DestinationCodeId. Set the Direction property to Descending. Right-click the QryGbyHavgRange23 node, and then click Save.

Next is an image of how the finished query looks when it is fully expanded in the AOT:

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.