Nov 16, 2012

Join by Query

static void CustTableSales(Args _args)
{
    Query                             query;
    QueryRun                       queryrun;
    QueryBuildDataSource     qbds1;
    QueryBuildDataSource     qbds2;
    QueryBuildRange             qbr1;
    QueryBuildRange             qbr2;
    CustTable                        custTable;
    ;
    query     = new query();
    qbds1    =   query.addDataSource(tablenum(CustTable));
    qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
    qbr1    = qbds1.addRange(fieldnum(custTable,custGroup));
    qbr1.value(queryvalue('10'));

    qbr2     =  qbds1.addRange(fieldnum(custTable,Blocked));
    qbr2.value(queryvalue(CustVendorBlocked::No));
    qbds2   = qbds1.addDataSource(tablenum(SalesTable));
    qbds2.relations(false);
    qbds2.joinMode(joinmode::ExistsJoin);
    qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
    queryrun    = new queryrun(query);

    while(queryrun.next())
    {
    custTable   = queryrun.get(tablenum(custTable));
    info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name));
    }
}

No comments: