Hi All,
I'm working on a query of 3 related table: InventSum, InventDim, and InventBatch. I need to sum the Available Qty in InventSum with grouping InventDim, at the same time fetch the data in FEFO sequence.
After apply SUM selection and sortfields for grouping, I'm trying to sort the last table as primary sorting.
I found that once I have set the table OrderMode with GroupBy, I could not add sorting or set the primary sorting field. And if I don't group the related table, the data is not loaded.
Refer my code below
static void KZ_QueryRunTest(Args _args)
{
Query querySum;
QueryRun queryRunSum;
QueryBuildDataSource qDBSum,qDBDim,qDBBatch;
QueryBuildRange queryRange;
InventSum inventSum;
InventDim inventDim;
InventBatch inventBatch;
InventDimParm InventDimParm1,InventDimParm2;
ItemId itemId='M~Test_Batch';
InventLocationId inventLocationId = '01';
InventSiteId inventSiteId = '01';
;
querySum = new Query();
qDBSum = querySum.addDataSource(tablenum(InventSum));
qDBDim = qDBSum.addDataSource(tablenum(InventDim));
qDBDim.joinMode(JoinMode::InnerJoin);
qDBDim.relations(true);
qDBBatch = qDBDim.addDataSource(tablenum(InventBatch));
qDBBatch.joinMode(JoinMode::InnerJoin);
qDBBatch.relations(true);
queryRange = qDBSum.addRange(fieldnum(InventSum,ItemId));
queryRange.value(queryValue(itemId));
queryRange = qDBDim.addRange(fieldnum(InventDim,InventSiteId));
queryRange.value(queryValue(inventSiteId));
queryRange = qDBDim.addRange(fieldnum(InventDim,InventLocationId));
queryRange.value(queryValue(inventLocationId));
InventDimParm1.initValue();
InventDim.initValue(); InventDim.InventSiteId = inventSiteId; InventDim.inventLocationId = inventLocationId;
InventSum::queryAddSumFields(qDBSum);
InventDim::queryDatasourceAddRangeSortFromParms(qDBDim,false,InventDim::findOrCreate(InventDim),InventDimParm1,InventDimParm::activeDimFlag('SWLB'));
qDBDim.addSortField(fieldnum(InventDim,InventBatchId));
qDBDim.orderMode(OrderMode::GroupBy);
qDBBatch.addSortField(fieldnum(InventBatch,ExpDate));
qDBBatch.addOrderByField(fieldnum(InventBatch,ExpDate));
qDBBatch.orderMode(OrderMode::GroupBy);
queryRunSum = new QueryRun(querySum);
//queryRunSum.run();return;
While (queryRunSum.next())
{
InventSum = queryRunSum.get(tablenum(InventSum));
inventDim = queryRunSum.get(tablenum(InventDim));
InventBatch = queryRunSum.get(tablenum(InventBatch));
info(strfmt('Batch: %1, ExpDate: %2',InventDim.inventBatchId,InventBatch.expDate));
}
}
