I have a batch where I receive a query object, which I want to extend.
I do this with the following code:
qbds2 = query.dataSourceTable(tablenum(TMSTransportationTender)).addDataSource(tablenum(PurchTable));
qbds2.joinMode(JoinMode::NoExistsJoin);
qbds2.addLink(fieldnum(TMSTransportationTender, RefId), fieldnum(PurchTable, TenderId));
qbds2.addRange(fieldnum(PurchTable, TenderId)).value(enum2value(NoYes::No));
QueryRun queryRun = new QueryRun(query);
When afterwards I inspect the QueryRun object, I can see the following Diry SQL:
SELECT CarrierCode, LoadId, RefId, RequestedRate, RequestedPickupDateTime
FROM TMSTransportationTender(TMSTransportationTender)
WHERE ((TenderStatus = 4)) AND ((TenderRefType = 0))
JOIN *
FROM WHSLoadTableLatestTransportationTenderView(WHSLoadTableLatestTransportationTenderView) ON TMSTransportationTender.TransportationOrderNum = WHSLoadTableLatestTransportationTenderView.LastTransportationOrderNum
JOIN AccountNum, LoadId
FROM WHSLoadTable(whsLoadTable) ON TMSTransportationTender.RefId = WHSLoadTable.LoadId AND ((LoadStatus = 6))
WHERE ((TMSTransportationTender(TMSTransportationTender).RefId = N'NL0001951'))
NOTEXISTS JOIN *
FROM PurchTable(PurchTable_1)
WHERE TMSTransportationTender.RefId = PurchTable.TenderId
AND ((TenderId = N'No'))
I can see that it seems that the NOT EXISTS clause is actually added to SQL, but when I continue I receive a result which I should NOT receive.
The row with RefId 'NL0001951' (the only one returned since I search for it via the batch parameters) should NOT be returned, as there are multiple Purchase Orders in the database referring to this RefId.
Pasting the Dirty SQL query to normal SQL also shows that query returning no data, as expected.
Does anyone know whether Im missing something or why this is not working?
Expected real SQL:
SELECT CarrierCode, LoadId, RefId, RequestedRate, RequestedPickupDateTime
FROM TMSTransportationTender t
INNER JOIN WHSLoadTableLatestTransportationTenderView ON t.TRANSPORTATIONORDERNUM = WHSLoadTableLatestTransportationTenderView.LastTransportationOrderNum
INNER JOIN WHSLoadTable ON t.RefId = WHSLoadTable.LoadId
WHERE ((TenderStatus = 4)) AND ((TenderRefType = 0)) and (t.RefId = N'NL0001951') AND ((LoadStatus = 6)) AND
NOT EXISTS(SELECT TENDERID FROM PurchTable p WHERE p.TENDERID = t.REFID)
