Group By Using Query Class using Two Tables...Kindly Help Me :

static void SSIPL(Args _args)
{
Query q;
QueryRun qr;
QueryBuilddataSource qbds;
QueryBuildRange qbr;
InventTrans _inventTrans;
InventTrans _inventTrans1;
ProdBom _prodBom;
ProdBom _prodBom1;
InventDim _inventDim;
;

q = new query();
qbds =q.addDataSource(tableNum(InventTrans));
qbds =qbds.addDataSource(tableNum(InventDim));
qbds.joinMode(JoinMode::InnerJoin);
qbds.relations(false);
qbds.addLink(fieldNum(InventTrans, InventDimID),
fieldNum(InventDim, InventDimID));
qbds.addSelectionField(FieldNum(InventTrans,ItemId),FieldNum(InventDim,InventColorId));
qbds.addSelectionField(FieldNum(InventTrans,Qty) ,Selectionfield::Sum);
qbds.orderMode(ordermode::GroupBy);
qbds.addSortField(fieldNum(InventTrans,ItemId),fieldNum(InventDim,InventColorId));
qr = new QueryRun(q);

while (qr.next())
{
_inventTrans = qr.get(tablenum(InventTrans));
_inventDim = qr.get(tableNum(InventDim));
print _inventTrans.ItemId + ‘—’ + num2str(_inventTrans.Qty,4,2,0,0) + _inventDim.InventColorId;

pause;

}

}

Its is not working …

I want to group by Item Id and InvenColourId,using two data sources and print the sum (qty) from inventTrans

kindly help there is not a single tutorial available in INTERNET related to this…PROBLEM;

The first obvious bug is that you call qbds.addSelectionField(FieldNum(InventTrans,…) while qbds holds InventDim data source, therefore it will be grouped by a completely different field. You also don’t group by InventColorId nor use any aggregation function for it, so it will be always empty.

If you call info(q.dataSourceNo(1).toString())), you’ll get the following query - the bugs are obvious from that:

SELECT FIRSTFAST * FROM InventTrans
JOIN FIRSTFAST SUM(inventSerialId) FROM InventDim
GROUP BY InventDim.inventDimId DESC
WHERE InventTrans.inventDimId = InventDim.inventDimId

Sir kindly explain the solution of above problem…

also post possible codes for above problem :slight_smile:

thankx in advance… :smiley:

After this block of code, qbds is a datasource for InventDim table:

qbds =q.addDataSource(tableNum(InventTrans));
qbds = qbds.addDataSource(tableNum(InventDim));

The following code tries to access the field in InventDim table that has the same ID as Qty field in InventTrans table, which is obvious nonsense.

qbds.addSelectionField(FieldNum(InventTrans,Qty) ,Selectionfield::Sum);

Your code should look somehow like this:

inventTransDs = q.addDataSource(tableNum(InventTrans));
inventDimDs = inventTransDs.addDataSource(tableNum(InventDim));
…
inventTransDs.addSelectionField(fieldNum(InventTrans, Qty), SelectionField::Sum);

About the other problem, you have to either group by InventColorId or use to an aggregation function, such as Max. You use both grouping and aggregation functions in your code, so you obviously know how to do that.

sit thanks a lot

i m going to try :slight_smile:

Yes…Yes!! Its working.