Dynamic Query Example;-

Dynamic Aggregate Functions
=================================================================
static void dynamicselectaggregate(Args _args)
{
    MCustTable mCustTable;
    Query query;
    QueryBuildDataSource qbds;
    QueryBuildRange qbr;
    QueryRun qr;

    query = new query();
    qbds = query.addDataSource(Tablenum(mCustTable));
    //qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::sum);
   // qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::Avg);
    //qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::Max);
    //qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::Min);
    //qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::Count);
   // qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::Database);

   //qbds.addSelectionField(fieldNum(mCustTable,MPRId),SelectionField::ComputedColumn);
    //qbds.addGroupByField(fieldNum(mCustTable,MSid));
    //qbr=qbds.addRange(fieldNum(mCustTable,MSid));
    //qbr.value("100..200");
    qr = new QueryRun(query);
=======================================================================
Dynamic Joins:-
==============================================
static void dynamicjoin(Args _args)
{
    MCustTable              mCustTable;
    MSalesTable             mSalesTable;
    Query                   query;
    QueryBuildDataSource    qbds1,qbds2;
    QueryBuildRange         qbr;
    QueryRun                qr;


    query = new query();
    qbds1 = query.addDataSource(tableNum(mCustTable));
    //qbds1.addRange(fieldNum(mCustTable,MSid));
    qbds2= qbds1.addDataSource(tableNum(mSalesTable));
    //qbds2.joinMode(JoinMode::InnerJoin);
   // qbds2.joinMode(JoinMode::OuterJoin);
    //qbds2.joinMode(JoinMode::ExistsJoin);
    qbds2.joinMode(JoinMode::NoExistsJoin);
   
    qbds2.relations(true);
    qr = new QueryRun(query);

    while (qr.next())
    {
        mSalesTable = qr.get(tableNum(mSalesTable));
        info(strFmt("%1",mSalesTable.MSId));
    }

}
========================================================================
Dynamic Query:-
-------------------------------------------------------------------------------------------------

static void CopyDynamicsQueryEx(Args _args)
{
    Query query;
    QueryBuildDataSource    qbds,qbds1;
    QueryBuildRange qbr, qbrAccNO;
    QueryRun    qr;
    CustTable       custtable;

    query = new query();
    qbds = query.addDataSource(tableNum(CustTable));
    qbds1=query.addDataSource(tableNum(SalesTable));
    qbds.addOrderByField(fieldNum(custtable,AccountNum), SortOrder::Descending);
    qbr = qbds.addRange(fieldNum(CustTable,CustGroup));
   // qbr.value("10");

   // qbrAccNO = qbds.addRange(fieldNum(CustTable,CreatedDateTime));
   // qbrAccNO.value(queryRange("1/4/2009","1/6/2009"));
    qr = new QueryRun(query);
    while(qr.next())
    {
        custtable = qr.get(tableNum(CustTable));
        info(strFmt("%1 - %2 - %3", custtable.AccountNum,custtable.CustGroup,custtable.createdDateTime));
    }
}
===================================================================
Select Statements Using Dynamic Query:-
===========================================================
static void dynamicselectex(Args _args)
{
    MCustTable              mCustTable;
    Query                   query;
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    QueryRun                qr;

    query= new query();
    qbds = query.addDataSource(tableNum(MCustTable));
   // qbds.addOrderByField(fieldNum(mCustTable,MSId),SortOrder::Descending);
    qbds.addOrderByField(fieldNum(mCustTable,MCustName),SortOrder::Descending);
    //qbds.firstOnly(true);
    //qbds.firstFast(true);
    //qbds.update(true);
    qbr =qbds.addRange(fieldNum(mCustTable,MCustName));
    //qbr.value("abc");

    qr = new QueryRun(query);

    while (qr.next())
    {
        mCustTable = qr.get(tableNum(mCustTable));
        info(strFmt("%1 %2",mCustTable.MCustName,mCustTable.MSid));
    }


}

=============================================================
Joins Examples:-- 06-04-2016
--------------------------------------------------------------------------------------

static void M_joinsEx(Args _args)
{
    MCustTable mCustTable;
    MSalesTable mSalesTable;
    MPurchTable mPurchTable;

    while select mSalesTable join mCustTable
            where mSalesTable.MSId == mCustTable.MSid
    {
        info(strFmt("%1,%2",mSalesTable.MSId,mCustTable.MSid));
    }
   
    while select mCustTable join mSalesTable
            where mCustTable.MCustName == mSalesTable.MCustName
    {
        info(strFmt("%1,%2",mCustTable.MCustName,mSalesTable.MCustName));
    }

    while select mSalesTable outer join mCustTable
            where mSalesTable.MSId == mCustTable.MPRId
    {
        info(strfmt("%1,%2",mSalesTable.MSId,mCustTable.MPRId));

    }
   
    while select mSalesTable outer join mCustTable
            where mSalesTable.MCustName == mCustTable.MCustName
    {
        info(strFmt("%1,%2",mSalesTable.MCustName,mCustTable.MCustName));
    }
       
    while select mCustTable exists join  mPurchTable
            where mCustTable.MCustName == mPurchTable.MCustName
    {
        info(strFmt("%1,%2",mCustTable.MCustName,mPurchTable.MCustName));
    }
   
        while select mCustTable notexists join mPurchTable
              where mCustTable.MCustName == mPurchTable.MCustName
    {
        info(strFmt("%1,%2",mCustTable.MCustName,mPurchTable.MCustName));
    }
       
   

}

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

dynamicquery examplesss

--------------------------------------------------------------------------

static void dynamicqueryExmp1(Args _args)

{

    Query query;

    QueryBuildDataSource qbds;

    QueryBuildRange qbr;

    QueryRun qr;

    MCustTable mcusttable;

    query   = new query();

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

   // qbr     =qbds.addRange(fieldNum(MCustTable,MPRId));

   // qbr     = qbds.addRange(fieldNum(MCustTable,MSId));

    qbr     = qbds.addRange(fieldNum(MCustTable,MCustName));

    qbds.addOrderByField(fieldNum(MCustTable,MSId),SortOrder :: Descending);

    qbds.addOrderByField(fieldNum(MCustTable,MCID),SortOrder ::Descending);

    qbr.value(queryRange("1","1000"));

    qbr.value("a*");

    //qbds.addGroupByField(fieldNum(mcusttable,MSId));

    qr      = new QueryRun(query);

    while (qr.next())

    {

        mcusttable = qr.get(tableNum(MCustTable));

        info(strFmt("MCId - %1, McustName - %2, MSId - %3, MPRId - %4",mcusttable.MCId,mcusttable.MCustName,mcusttable.MSId,mcusttable.MPRId));

    }

}

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

Multi DATA Source in Dynamic query

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

Query                                    query = new Query();

    QueryBuildDataSource     queryBuildDSSalesTable;

    QueryBuildDataSource     queryBuildDSSalesLine;

    QueryBuildRange              queryBuildRange;

    QueryRun                            queryRun;

    SalesLine                            salesLine;

    ;

    

    // Setup the primary datasource.

    queryBuildDSSalesTable  = query.addDataSource(tablenum(SalesTable));

    queryBuildDSSalesTable.addRange(fieldnum(SalesTable,SalesId)).value('XXXXXX');

    // Setup the secondary (joined) datasource.

    queryBuildDSSalesLine   = queryBuildDSSalesTable.addDataSource(tablenum(SalesLine));

    queryBuildDSSalesLine.joinMode(JoinMode::InnerJoin);

    queryBuildDSSalesLine.relations(true);

    queryRun = new QueryRun(query);

    while(queryRun.next())

    {

        salesLine   = queryRun.get(tablenum(SalesLine));

        info(strfmt('%1',salesLine.SalesId));

    }