Dynamic Queries in Ax 2012

                                                                                

Query

            The Query class embodies the structure of a query. Objects of this type are not used for fetching records from the database. Instead, use a QueryRun object that may be assigned a query object. The static behavior is defined by the Query class.

                       Queries contain one or more data sources that correspond to tables in the database. The data sources are specified by using QueryBuildDataSource objects.

The following example creates a query object that is used to create a QueryRun object.     

Query q = new Query (QueryStr(queryname));

    // Use the query to build a queryRun object.

QueryRun qr = new QueryRun (q);

     // Traverse some records.

 while (qr.next())

 {

        // ...

  }                               

QueryBuildDataSource

The QueryBuildDataSource class provides the building blocks that queries are made of.

Ex :

                                              QueryBuildDataSource ds;

                                              Query q = new Query();                       

                                              ds = q.addDataSource(TableNum(TableName));}

QueryFilter

QueryFilter class gives you a way to filter the result set of an outer join to obtain a different outcome than you can obtain from X++ SQL. The QueryFilter class applies the filters to the WHERE clause in standard SQL, instead of to the ON keyword of the JOIN clause. Filters that are specified on the WHERE clause are applied later in the process than filter on the ON keyword. The Application Object Server (AOS) sends standard SQL to the underlying database management system. Applying the filters on the WHERE clause can produce a result set that contains no records that are built from default values.

As an alternative way to filter an outer join query, you can use the QueryBuildRange class. It produces results that match what you get from X++ SQL with an OUTER JOIN.

static void Raj_Queryfilter(Args _args)

{

    Query                   query = new Query();

    QueryBuildDataSource    salesTableDS;

    QueryBuildDataSource    salesLineDS;

    QueryBuildRange         qbr;

    QueryFilter             qf;

    QueryRun                qr;

    SalesTable              salesTable;

    SalesLine               salesLine;

 

    salesTableDS    = query.addDataSource(tableNum(SalesTable));

    salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));

   // salesLineDS.relations(true);

    salesLineDS.joinMode(JoinMode::OuterJoin);

    salesLineDS.addLink(fieldNum(SalesTable, SalesId), fieldNum(SalesLine, SalesId));

    //qbr = salesLineDS.addRange(fieldNum(SalesLine, SalesId));

    //qbr.value('000006');

    qf = query.addQueryFilter(salesLineDS,fieldStr(SalesLine, SalesId));

    qf.value('000006');

    qr = new QueryRun(query);

    while(qr.next())

    {

        salesTable= qr.get(tableNum(salesTable));

        salesLine = qr.get(tableNum(salesLine));

  

        info(strFmt("%1---%2",salesTable.SalesId,salesLine.SalesId));

    }

}

ü  Matched records will be displayed.

ü If Query Build range is applied

QueryBuildDynalink

Contains information regarding a relation to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.

QueryBuildFieldList

                       Defines which fields are returned from the database. The default is that the field list is dynamic, which returns all fields from the data source table, map, or view. Each data source has only one QueryBuildFieldList object, which contains information on all selected fields. It’s possible to specify aggregate functions like SUM, COUNT, and AVG on the field list object.

QueryBuildLink

                         Specifies the relation between the two data sources in the join. Can only exist on a child data source.

ü The QueryBuildLink class enables for the creating, reading, updating, and deleting of X++ code.

QueryBuildStaticLink

                        QueryBuildStaticLink class provides the information about the static links that are defined on a QueryBuildDataSource class

QueryHavingFilter

                      In Sql, Having clause  mainly used whenever we are using Aggregate Functions that time Where clause will not be used,that time Having clause is used. Coming to Ax we are not having Having clause, in ax 2012 r3 they implemented QueryHavingFilter Class.

Scenario

The CUSTTABLE table has a field called CUSTGROUP, indicating the customer group the customer belongs to. We would like to get a list of all customer groups that have less than 4 customers in them.

Traditionally, in AX queries, we can group by the CUSTGROUP field, COUNT the RecIds. However, there was no way to filter on that counted RecId field. However, in SQL, the having statement gives you that ability:

SELECT CUSTGROUP, COUNT(*) FROM CUSTTABLE

        GROUP BY CUSTGROUP  HAVING COUNT(*) < 4

In Ax

static void Raj_Queryhavingfilter(Args _args)

{

    Query                   query;

    QueryBuildDataSource    datasource;

    QueryBuildRange         range;

    QueryHavingFilter       havingFilter;

    QueryRun                queryRun;

    int                     counter = 0, totalCounter = 0;

    CustTable               custTable;

    query = new Query();

    datasource = query.addDataSource(tableNum(CustTable));

    datasource.addSelectionField(fieldNum(CustTable, RecId),

            SelectionField::Count);

    datasource.orderMode(OrderMode::GroupBy);

    datasource.addGroupByField(fieldNum(CustTable, CustGroup));

    havingFilter = query.addHavingFilter(datasource, fieldStr(custTable, RecId),

            AggregateFunction::Count);

    havingFilter.value('< 4');

    queryRun = new QueryRun(query);

    while (queryRun.next())

    {

        custTable = queryRun.getNo(1);

        info(strFmt("Group %1: %2", custTable.CustGroup, custTable.RecId));

    }

}

Output