Friday, July 5, 2013

Example for using a tempDB table in a Query

Below job is adding and retrieving data into tempDB via query object.

static void TempDB(Args _args)
{
    PurchLineMarkTmp    tmpUnionedResult;
    InventTable         inventTable;
    InventTableModule   inventTableModule;
    Query               query;
    QueryRun            queryRun;

    // Execute insert_recordset with TempDB table as a target
     insert_recordset tmpUnionedResult(PurchLine)
        select RecId from inventTable
            exists join inventTableModule
                where   inventTableModule.ModuleType == ModuleInventPurchSales::Sales
                    && inventTable.ItemId == inventTableModule.ItemId
                    && inventTableModule.Price != 0; 

    query = new query();
    query.addDataSource(tableNum(PurchLineMarkTmp));
    queryRun = new QueryRun(query);
 
    // Force queryRun to fetch from the right temporary table
    queryRun.setRecord(tmpUnionedResult);

    // Iterate all registered quantities
    while (queryRun.next())
    {
        tmpUnionedResult   = queryRun.get(tablenum(PurchLineMarkTmp));
 
        info (strFmt('%1', tmpUnionedResult.PurchLine));
    }
}


 
Post a Comment