Querybuild, join refer back to first tabel does not work in code

Question

I have this SQL statement :

select * from AX2009_DEV.dbo.WMSINVENTTRANSDOCK join AX2009_DEV.dbo.SALESTABLE on INVENTTRANSREFID = salesid join AX2009_DEV.dbo.INVENTDIM on WMSINVENTTRANSDOCK.inventDimId = inventDim.inventDimId

I am trying to write this in code :

qbdWMSInventTransDock = query.addDataSource(tableNum(WMSInventTransDock));

qbdSalesTable = qbdWMSInventTransDock.addDataSource(tableNum(SalesTable));
qbdSalesTable.addLink(fieldNum(WMSInventTransDock, InventTransRefId), fieldNum(SalesTable, SalesId));
qbdSalesTable.joinMode(JoinMode::InnerJoin);

qbdInventDim = qbdWMSInventTransDock.addDataSource(tableNum(InventDim));
qbdInventDim.addLink(fieldNum(WMSInventTransDock, InventDimId), fieldNum(InventDim, InventDimId));
qbdInventDim.joinMode(JoinMode::InnerJoin);

But when I debug I can see the SQL statement "resets" as soon as I try to connect InventDim to WMSInventTransDock.

I want to do it this way and not write a select statement because I want to be able to do this later on :

if (inventSiteId)
{
    qbr = qbdInventDim.addRange(fieldNum(InventDim, InventSiteId));
    qbr.value(inventSiteId);
}
if (inventLocationId)
{
    qbr = qbdInventDim.addRange(fieldNum(InventDim, InventLocationId));
    qbr.value(inventLocationId);
}

Alex Kwitny · Answer 1 · 2013-10-02T15:51:53.880

0

I think you should establish your link/joinmode first, then build your ranges. It might be wise to create separate qbr, qbr2, qbr3 variables too, that way you can reference them, instead of constantly re-initializing the same one...which I wouldn't even think works, but it may since you're applying to the same query. Not a good way to do it at all though.

Try re-arranging the lines like this:

qbdWMSInventTransDock = query.addDataSource(tableNum(WMSInventTransDock));
qbdInventDim = qbdWMSInventTransDock.addDataSource(tableNum(InventDim));
qbdInventDim.joinMode(JoinMode::InnerJoin);
qbdInventDim.addLink(fieldNum(WMSInventTransDock, InventDimId), fieldNum(InventDim, InventDimId));

qbr = qbdWMSInventTransDock.addRange(fieldNum(WMSInventTransDock, ItemId));
qbr.value(_itemId);

qbr = qbdWMSInventTransDock.addRange(fieldNum(WMSInventTransDock, InventTransType));
qbr.value(queryValue(InventTransType::Sales));

qbr = qbdWMSInventTransDock.addRange(fieldNum(WMSInventTransDock, WMSExpeditionStatus));
qbr.value(queryValue(WMSExpeditionStatus::Complete));

edited Oct 02 '13 at 15:51

answered Oct 01 '13 at 21:21

Alex Kwitny

11,211
2
49
71

Since you're setting custom links, have you tried doing `qbdWMSInventTransDoc.clearRanges();` and `qbdWMSInventTransDoc.clearLinks();` first, and maybe try `qbdInventDim.fetchMode(QueryFetchMode::One2One);` – Alex Kwitny Oct 02 '13 at 16:15
as soon as this line is executed qbdInventDim = qbdWMSInventTransDock.addDataSource(tableNum(InventDim)); the sql command is reset to the first value, losing all connection made before – PhDJ Oct 03 '13 at 12:22
It's due to the `qbdSalesTable.joinMode(JoinMode::InnerJoin)` for some reason. Would `JoinMode::ExistsJoin` and `*.fetchMode(QueryFetchMode::One2One)` work? See http://axdaily.blogspot.com/2010/04/magical-fetchmode-property.html – Alex Kwitny Oct 07 '13 at 18:24

Querybuild, join refer back to first tabel does not work in code

1 Answers1