StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery object not working as expected?
text
Body
copied!<p>This code should be able to run on any AX system (change item & warehouse). The issue is that CustInvoiceJour is not being returned sometimes. What is odd, is if I just do a "select custInvoiceJour" with the same relations, it DOES find it? How is this possible?</p> <pre><code>static void Job59(Args _args) { CustInvoiceTrans custInvoiceTrans; CustInvoiceJour custInvoiceJour; InventTable inventTable; QueryBuildRange rangeItem; Query query; QueryBuildDataSource qbds1; QueryBuildDataSource qbds2; QueryBuildDataSource qbds3; QueryRun qr; ; query = new Query(); qbds1 = query.addDataSource(tablenum(CustInvoiceTrans)); qbds2 = qbds1.addDataSource(tablenum(CustInvoiceJour)); qbds2.relations(true); qbds2.addRange(fieldnum(CustInvoiceJour, SalesType)).value('!' + enum2str(SalesType::ReturnItem)); rangeItem = qbds1.addRange(fieldnum(CustInvoiceTrans, ItemId)); qbds1.addRange(fieldnum(CustInvoiceTrans, InvoiceDate)).value(queryRange(@'8/1/2011', @'8/31/2011')); qbds2.orderMode(OrderMode::OrderBy); qbds2.addOrderByField(fieldnum(CustInvoiceJour, DlvCountryRegionId)); qbds2.addOrderByField(fieldnum(CustInvoiceJour, DlvState)); qbds3 = qbds1.addDataSource(tablenum(InventDim)); qbds3.relations(true); qbds3.joinMode(JoinMode::ExistsJoin); qbds3.addRange(fieldnum(InventDim, InventLocationId)).value(SysQuery::value('FG')); select firstonly inventTable where inventTable.ItemId == '44831'; info (strfmt("%1", inventTable.ItemId)); rangeItem.value(inventTable.ItemId); qr = new QueryRun(query); while (qr.next()) { custInvoiceTrans = qr.get(tablenum(CustInvoiceTrans)); custInvoiceJour = qr.get(tablenum(CustInvoiceJour)); if (!custInvoiceJour) { info ("Not found"); select firstonly custInvoiceJour where custInvoiceJour.SalesId == custInvoiceTrans.SalesId && custInvoiceJour.InvoiceId == custInvoiceTrans.InvoiceId && custInvoiceJour.InvoiceDate == custInvoiceTrans.InvoiceDate && custInvoiceJour.numberSequenceGroup == custInvoiceTrans.numberSequenceGroup; if (custInvoiceJour) info("Found it"); } } } </code></pre> <p>The debugger shows these as the queries:</p> <pre><code>NAME: qbds1 VALUE: SELECT * FROM CustInvoiceTrans ORDER BY CustInvoiceJour.DlvCountryRegionId ASC, CustInvoiceJour.DlvState ASC WHERE ((ItemId = N'44831')) AND ((InvoiceDate>={ts '2011-08-01 00:00:00.000'} AND InvoiceDate<={ts '2011-08-31 00:00:00.000'})) EXISTS JOIN * FROM InventDim WHERE CustInvoiceTrans.InventDimId = InventDim.inventDimId AND ((InventLocationId = N'FG')) NAME: qbds2 VALUE: SELECT * FROM CustInvoiceJour WHERE CustInvoiceTrans.SalesId = CustInvoiceJour.SalesId AND CustInvoiceTrans.InvoiceId = CustInvoiceJour.InvoiceId AND CustInvoiceTrans.InvoiceDate = CustInvoiceJour.InvoiceDate AND CustInvoiceTrans.numberSequenceGroup = CustInvoiceJour.numberSequenceGroup AND ((NOT (SalesType = 4))) NAME: qbds3 VALUE: SELECT * FROM InventDim WHERE CustInvoiceTrans.InventDimId = InventDim.inventDimId AND ((InventLocationId = N'FG')) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload