X++:
static void PurchLineQuery(Args _args)
{
QueryBuildDataSource qbdsPurchLineSum,qbdsPurchTable,qbds;
QueryBuildRange qbr;
Query q = new Query();
QueryRun qr;
PurchLine PurchLine;
#define.itemIdParm('123')
;
qbdsPurchLineSum = q.addDataSource(tableNum(PurchLine));
qbdsPurchLineSum.addSelectionField(fieldNum(PurchLine,PurchQty),SelectionField::Sum);//внимание на единицы измерения! может даже лучше идти по inventTrans
qbr = sysQuery::findOrCreateRange(qbdsPurchLineSum,fieldNum(PurchLine,PurchStatus));
qbr.value(queryValue(PurchStatus::Backorder));
qbr = sysQuery::findOrCreateRange(qbdsPurchLineSum,fieldNum(PurchLine,ItemId));
qbr.value(queryValue(#itemIdParm));
qbdsPurchTable = qbdsPurchLineSum.addDataSource(tableNum(PurchTable));
qbdsPurchTable.joinMode(joinMode::ExistsJoin);
qbdsPurchTable.fetchMode(QueryFetchMode::One2One);
qbdsPurchTable.relations(true);
qbr = sysQuery::findOrCreateRange(qbdsPurchTable,fieldNum(PurchTable,PurchStatus));
qbr.value(queryValue(PurchStatus::Backorder));
qbds = qbdsPurchTable.addDataSource(tableNum(PurchLine));
qbds.joinMode(joinMode::NoExistsJoin);
qbds.fetchMode(QueryFetchMode::One2One);
qbds.relations(true);
qbr = sysQuery::findOrCreateRange(qbds,fieldNum(PurchLine,ItemId));
qbr.value(queryValue(#itemIdParm));
qbr = sysQuery::findOrCreateRange(qbds,fieldNum(PurchLine,RecId));
qbr.value(strFmt('((%1.%2 != "%3") || (%4.%5 == "%5"))',
qbds.name(),
fieldId2name(tableNum(PurchLine),fieldNum(PurchLine,PurchStatus)),
queryValue(PurchStatus::Backorder),
qbds.name(),
fieldId2name(tableNum(PurchLine),fieldNum(PurchLine,Blocked)),
queryValue(NoYes::Yes)
)
) ;
info(qbdsPurchLineSum.toString());
qr = new QueryRun(q);
while (qr.next())
{
PurchLine = qr.get(PurchLine.TableId);
info(strFmt("%1 %2",PurchLine.PurchId,PurchLine.PurchQty));
}
}
примерно так (4.0 в т.ч.)