Dynamic Query in X++

Query introduction:-

Query:-query are used to retrieve data from database.

queries are two types :-1.static query 

                                    2.dynamic query

Static query:-A static query is a result that never changes. It is based on information that matched the criteria from the particular moment when the query was made. 

 only specific range purpose use static queries .

create Static query:- for example this static query

The Application Object Tree (AOT) provides a graphical interface for creating queries. These are known as static queries 

               1.In the AOT, right-click Queries, and then click New Query. 

               2.Right-click Data Dictionary, and then click Open New Window. 

  

               3.Drag a table, map, or view from the second window to the Data Sources node under the new query in the first window.  

1.query-- data source -field property --by default Dynamics - unselected -- if your didn't change this property, the below error msg populate .

2.query-- data source -field property --set to dynamics fields------- yes--if you select in lookup --yes all the  table fields are visible.

3.query-- data source -field property --set to dynamics fields------- no--if you select in look up --no the table fields are not visiable.

In query data source first added parent data source ,in this parent data source you can add multiple child data sources.

Dynamic queries:-

A dynamic query is updated with its criteria each time you click into the query. 

                   At the top of the page, you can see a date that indicates when the query was last updated.   

                 dynamically apply the range that time we can use the dynamic queries. 

  

                1.jobs location write the code . 

QUERY OBJECT MODE:-1.QOM contains classes and to define & run a query 

                   2.these objects are used to define the query data source, the fields RETURNED 

  

  

To create Dynamic Querys In ax we will use following classes. 

  

1.QUERY                      --------->      to create the structure for the query 

2.QUERYBUILDDATASOURCE       -------->       to add the DataSource the query 

3.QUERYRANGE                 ---------->     to apply ranges 

4.QUERYFILTER                 --------->     worked as where class in a standard Sql statement 

5.QUERYBUILDLINK             ---------->     to add and link two data sources. 

6.QUERYHAVINGFILTER          ---------->    used for aggregate functions. 

    *adddatasource--to add data source

    *addlink----to use realtions

   *addSelectionField--

   *orderMode:: ------order by group by

  *addGroupByField--filed adding

 *

query                                         q;

querybuilddatasource                 qbds;

querybuildrange                         qbr;

queryrun                                        qr;

Purchtable                                   purchtable;  

q = new query ();

qbds = q.adddatasource(tablenum(purchtable));

qbr    = qbds.addrange(fieldnum(purchtable,purchid));

qbr.vailue('101');

qr = new queryrun(q);

if(qr.promt())------- prompt() - if you mentioned prompt() method  automatically create one  dialogue box .in this dialogue box you can give your criteria.

{

while (qr.next())

{

info(strfmt("%1 - %2",PurchTable.PurchId,PurchTable.PurchName));

}

}

====================================================================

                      QUERYHAVINGFILTER 

static void Having_Filter(Args _args) 

{ 

    Query                                              q ;                                                            

    QueryBuildDataSource                qbd; 

    QueryBuildRange                         qbr; 

    QueryHavingFilte                          qhf; 

    QueryRun                                         qr; 

    Cust1 c; 

    q = new Query(); 

   qbd = q.addDataSource(tableNum(Cust1)); 

    qbd.addSelectionField(fieldNum(Cust1, RecId),SelectionField::Count); 

    qbd.orderMode(OrderMode::GroupBy); 

    qbd.addGroupByField(fieldNum(Cust1,salary)); 

    qhf = q.addHavingFilter(qbd,fieldStr(Cust1,RecId),AggregateFunction::Count); 

    qhf.value( ">= 10000"); 

    qr = new QueryRun(q); 

    while(qr.next()) 

    { 

        c = qr.getNo(1); 

        info(strFmt("group %1:%2",c.salary,c.RecId)); 

    } 

  

} 

===========================================================================================

 

                QUERYFILTER 

static void Queryfilter(Args _args) 

{ 

    Query q; 

    QueryBuildDataSource    qbd; 

    QueryBuildDataSource    qbd1; 

    QueryBuildLink          qbl; 

    QueryBuildRange         qbr; 

    QueryRun                qr; 

    QueryFilter              qf; 

    Sales                   s; 

    Order1                  o; 

  

    q = new Query(); 

    qbd = q.addDataSource(tableNum(Sales)); 

    qbd1 = qbd.addDataSource(tableNum(Order1)); 

    qbl = qbd1.addLink(fieldNum(Sales, SaleId),fieldNum(order1 ,salesid)); 

   qf = q.addQueryFilter(qbd1,fieldStr(Order1,SaleId)); 

    qf.value('2'); 

    qr= new QueryRun(q); 

    while(qr.next()) 

    { 

        s = qr.get(tableNum(Sales)); 

        o = qr.get(tableNum(Order1)); 

        info(strFmt("%1---%2",s.SaleId,o.SaleId)); 

    } 

++++ 

} 

=============================================================================