Dynamic Queries

 

To Select All Customers from Custtable

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbd;

    CustTable               custTable;

   

    q   = new Query();

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

    qbd.addSortField(fieldNum(CustTable,AccountNum),SortOrder::Ascending);

    qr = new QueryRun(q);

    while(qr.next())

    {

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

      info(strFmt("%1",custTable.AccountNum));

       

    }

   

}

   To Select Customers Between Range

static void CON_DynamicQuery(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q       = new Query();

    qbds    = q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);  // order by

    qbr     = qbds.addRange(fieldNum(custTable,AccountNum)); // For all customers

    qbr.value(queryRange('US-001','US-009')); // for particular range

  

    qr = new QueryRun(q);

    while(qr.next())

    {

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

        info(custTable.AccountNum);

       

    }

}

 

Where Condition

static void Job34(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

   

    q   = new Query();

    qbds = q.addDataSource(tableNum(custTable));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.addSortField(fieldNum(custTable,CustGroup),SortOrder::Descending);

    qbr = qbds.addRange(fieldnum(custTable,CustGroup));

    qbr.value(queryRange('10','30'));

   

    qr  = new QueryRun(q);

    while(qr.next())

    {

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

        info(strFmt("%1--%2",custTable.CustGroup,custTable.AccountNum));

    }

}

  CrossCompany

 

static void CON_DynamicStatement(Args _args)

{

    CustTable       custTable;

   

    Query                   query;          

    QueryBuildDataSource    qbds;

    QueryRun                qr;

   

    query   = new Query();

    query.allowCrossCompany(true);

   // query.addCompanyRange('USMF');

   // query.addCompanyRange('IBM');

    qbds  = query.addDataSource(tableNum(custTable));

    qr    = new QueryRun(query);

    while(qr.next())

    {

      

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

            info(strFmt('%1-%2',custTable.AccountNum,custTable.dataAreaId));

    

    }

   

}

 

Aggregate Function -- min,max,sum,count,computedcolumn,database

 

static void CON_Dynamic(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    qbds.addSelectionField(fieldNum(custTrans,AmountMST),Selectionfield::Min);

    qbds.addSelectionField(fieldNum(custTrans,RecId),SelectionField::Count);

   

    qr      = new QueryRun(q);

    while(qr.next())

    {

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

        info(strFmt("%1",custTrans.AmountMST));

    }

   

}

                              

  First only,first fast

static void CON_Dynamic(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q   = new Query();

    qbds= q.addDataSource(tableNum(custTable));

    qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    qbds.firstOnly(true);  //firstonly --firstfast

    qr = new QueryRun(q);

  

    while(qr.next())

    {

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

        info(strFmt("%1",custTable.AccountNum));

    }

}

 

    forUPdate

 

static void CON_DynamicforUpdate(Args _args)

{

    int                     i;

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    VendTable               vendTable;

    CustTable               custTable;

    CustTrans               custTrans;

    Con_Calc                obj;

    q   = new Query();

    qbds= q.addDataSource(tableNum(Con_Calc));

    //qbds.addSortField(fieldNum(custTable,AccountNum),SortOrder::Descending);

    //qbds.firstFast(true);

    qbds.addSortField(fieldNum(Con_Calc,Name));

    qr = new QueryRun(q);

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        obj.selectForUpdate(true);

        ttsBegin;

        if(obj.Name =="Siva")

        {

            obj.Name="Sivakumar";

            obj.update();

        }

        ttsCommit;

        info(strFmt("%1",obj.Name));

    }

}

   GroupBy

 

static void CON_DynamicGroupBy(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

   

    q = new Query();

    qbds = q.addDataSource(tableNum(custTrans));

    //qbds.addSortField(fieldNum(custTable,AccountNum));

    qbds.addSelectionField(fieldNum(custTrans, AmountMST),SelectionField::Max);         //Aggregate

    qbds.addGroupByField(fieldNum(custTrans,AccountNum),OrderMode::GroupBy);     //group by

      

    qr = new QueryRun(q);

  

    while(qr.next())

    {

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

        info(strFmt("%1--%2",custTrans.AccountNum,custTrans.AmountMST));

    }

   

}

 

     Joins

 

static void CON_DynamicJoin(Args _args)

{

    Query                   q;

    QueryRun                qr;

    QueryBuildDataSource    qbdsCustTable,qbdsCustTrans;

    QueryBuildRange         qbr;

    CustTable               custTable;

    CustTrans               custTrans;

    CON_Car                 car;

    CON_RentCar             rent;

    q   = new Query();

    qbdsCustTable = q.addDataSource(tableNum(CON_Car));                     //Data Source 1

    //qbdsCustTable.addSortField(fieldNum(CON_Car,CarId),SortOrder::Descending);

    qbdsCustTrans = qbdsCustTable.addDataSource(tableNum(CON_RentCar));    //Data Source 2

    qbdsCustTrans.relations(false);

    qbdsCustTrans.joinMode(JoinMode::InnerJoin);                          //Join Type

    qbdsCustTrans.addLink(fieldNum(CON_Car,CarId),fieldNum(CON_RentCar,CarId));

   

    qr = new QueryRun(q); 

    while(qr.next())

    {

      

        car = qr.get(tableNum(CON_Car));

        rent = qr.get(tableNum(CON_RentCar));

        info(strFmt("%1--%2",car.CarId,rent.CarId));

    }

}

 Full Text Index

 

static void CON_FullTextIndex(Args _args)

{

    Query           q;

    QueryRun        qr;

    QueryBuildDataSource    qbds;

    QueryBuildRange         qbr;

    Con_Calc                obj;

    q       = new Query();

    qbds    = q.addDataSource(tableNum(Con_Calc));

    qbr     = qbds.addRange(fieldNum(Con_Calc,Name));

    qbr.rangeType(QueryRangeType::FullText);

    qbr.value("Raj kumar");

    qr      = new QueryRun(q);

  

    while(qr.next())

    {

        obj = qr.get(tableNum(Con_Calc));

        info(obj.Name);

    }

}

 

  Multiple Tables Join

 

static void CON_DyamicQUeries(Args _args)

{

    Query                   query = new Query();

    QueryBuildDataSource    salesTableDS;

    QueryBuildDataSource    salesLineDS;

    QueryBuildDataSource    custTableDS;

    QueryRun                qr;

    CustTable               custTable;

    SalesTable              salesTable;

    SalesLine               salesLine;

  

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

   

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

    salesLineDS.relations(true);

    //salesLineDS.fetchMode(QueryFetchMode::One2One);

    salesLineDS.joinMode(JoinMode::InnerJoin);

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

    custTableDS     = salesTableDS.addDataSource(tableNum(CustTable));   

    custTableDS.relations(false);   

    custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

    //custTableDS.fetchMode(QueryFetchMode::One2One);

    custTableDS.joinMode(JoinMode::InnerJoin);

    //info(salesTableDS.toString());.

    qr = new QueryRun(query);

    while(qr.next())

    {

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

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

        CustTable = qr.get(tableNum(custTable));

   

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

    }

}