I created the following query.
This query is created because I need unique TMSTransportationTender/whsLoadTable combinations and I want to grab the most recent TMSTransportationTender record and therefore GROUPBY on whsLoadTable.RecId and then grab the MAX(TMSTransportationTender.RecId).
Afterwards I join another instance of the TMSTransportationTender to grab the other columns from the Max(RecId) row.
It seems the query itself returns the correct rows, because via the bactch filters I pass a TMSTransportation's ReferenceId, of which there are two.
In the Service's process method I have the following code:
while (queryRun.next())
{
try
{
ttsbegin;
TMSTransportationTender tMSTransportationTender = queryRun.get(tableNum(TMSTransportationTender),1);
TMSTransportationTender tMSTransportationTender2 = queryRun.get(tableNum(TMSTransportationTender),2);
WHSLoadTable whsLoadTable = queryRun.get(tableNum(WHSLoadTable));
CreatePurchaseOrdersForTendersService:: CreatePurchaseOrderForTender(tMSTransportationTender, whsLoadTable, shouldProcessDocumentStatusPurchaseOrder, shouldProcessDocumentStatusPurchaseInvoice);
ttscommit;
}
catch (Exception::Info)
{
continue;
}
}
The good thing is that I only enter the loop once (so the GROUPBY is working as intended, returning only 1 resultset) and the TMSTransportationTender2 contains the highest RecId of both the rows.
The whsLoadTable is filled correctly as well.
But the problem is that I expect TMSTransportationTender1 to contain the values for carriercode and loadId, as I set them in my query object, but it only returns NULL values.
Does anyone know what I am doing wrong or what could be missing here?
EDIT: add of SQL query that would be expected:
