You’re offline. This is a read only version of the page.
1,214
Hi. I'm building an X++ Query based on this SQL Server query:
select .........
from CUSTINVOICETRANS custinvoicetrans
full outer join CUSTINVOICEJOUR custinvoicejour
on custinvoicetrans.invoiceid = custinvoicejour.invoiceid
full outer join CUSTTABLE custtable
on custinvoicejour.invoiceaccount = custtable.accountnum
inner join SALESTABLE salestable
on custinvoicetrans.SALESID = salestable.SALESID
inner join SALESLINE salesline
on custinvoicetrans.SalesId = salesline.SalesId and custinvoicetrans.ItemId = salesline.ItemId
left outer join (select shipmentid, transrefid,containerno, dataareaid from PWAustraliaRetailChild where dataAreaId1 = 'GF' and status = '9') f
on custinvoicetrans.salesid = f.TRANSREFID
where
custinvoicetrans.itemid<>''
and custinvoicetrans.DATAAREAID='GF'
...by the way, PWAustraliaRetailChild is a View. My X++ Query is as follows:
QueryBuildRange rangeInvoice;
QueryBuildRange rangeStatus;
QueryBuildDataSource q1,q2,q3;
q1 = query.addDataSource(tableNum(CustInvoiceTrans));
rangeInvoice = q1.addRange(fieldNum(CustInvoiceTrans, ItemId));
rangeInvoice.value(queryNotValue(strFmt('%1' ,"")));
rangeInvoice.status(2); //range status = hidden
//CustInvoiceTrans -> CustInvoiceJour
q2 = q1.addDataSource(tableNum(CustInvoiceJour));
q2.addLink(fieldNum(CustInvoiceTrans, InvoiceId),fieldNum(CustInvoiceJour, InvoiceId));
q2.joinMode(JoinMode::OuterJoin); //outer
//CustInvoiceJour -> CustTable
q3 = q2.addDataSource(tableNum(CustTable));
q3.addLink(fieldNum(CustInvoiceJour, InvoiceAccount),fieldNum(CustTable, AccountNum));
q3.joinMode(JoinMode::OuterJoin);
//CustInvoiceTrans -> SalesTable
q2 = q1.addDataSource(tableNum(SalesTable));
q2.addLink(fieldNum(CustInvoiceTrans, SalesId),fieldNum(SalesTable, SalesId));
q2.joinMode(JoinMode::InnerJoin);
//CustInvoiceTrans -> SalesLine
q2 = q1.addDataSource(tableNum(SalesLine));
q2.addLink(fieldNum(CustInvoiceTrans, SalesId),fieldNum(SalesLine, SalesId));
q2.addLink(fieldNum(CustInvoiceTrans, ItemId),fieldNum(SalesLine, ItemId));
q2.joinMode(JoinMode::InnerJoin);
//CustInvoiceTrans -> PWAustraliaRetailChild
q2 = q1.addDataSource(tableNum(PWAustraliaRetailChild));
q2.addLink(fieldNum(CustInvoiceTrans, SalesId), fieldNum(PWAustraliaRetailChild, transRefId));
q2.joinMode(JoinMode::OuterJoin);
//PWAustraliaRetailChild.status @ wmsshipment.status == 9
rangeStatus = q2.addRange(fieldNum(PWAustraliaRetailChild, status));
rangeStatus.value(strFmt('%1' ,9));
rangeStatus.status(2); //hidden
My X++ Query didn't return the same result as the SQL Server one. Can you please help me point out where I did wrong? I checked multiple times and I'm pretty sure I have linked all join fields correctly. Is it because I didn't create full outer join? How exactly to create full outer join? Because as far as my understanding, full outer join is not supported in AX.
Thank You.
*This post is locked for comments
I have the same question (0)
Actually in SQL Server, the query still returns the same data even when I replaced the full outer join with left join. So no alternative to full outer join is needed. It returns same data when i removed the join to SalesTable as well. So SalesTable can be excluded.
I found that (actually my coworker did) AX will sometimes returning incorrect data when joining too much tables together in one single query. In this case, 5 Tables & 1 View are used. The solution is to simplify the query, make it as short as possible. This is how we did:
First, let me revise this whole query. The whole relationship is as follow:
CustInvoiceTrans -> CustInvoiceJour -> CustTable
CustInvoiceTrans -> SalesLine
CustInvoiceTrans -> PWAustraliaRetailChild (View)
1. Inside CustInvoiceTrans Table, there's already a method custInvoiceJour() & salesLine() provided to link to CustInvoiceJour & SalesLine Table respectively.
2. In CustInvoiceJour, there're also methods custTable_InvoiceAccount() & custTable_OrderAccount() to link to CustTable using the specified field.
Since those methods are available, we can exclude CustInvoiceJour, CustTable, SalesLine tables in the query. So in our new Query, we only need to join CustTrans & PWAustraliaRetailChild view.
To get data from CustInvoiceJour, CustTable & SalesLine, we defined these tables as follows:
custInvoiceTrans = queryRun.get(tableNum(CustInvoiceTrans));
custInvoiceJour = custInvoiceTrans.custInvoiceJour();
custTable = custInvoiceJour.custTable_OrderAccount();
salesLine = custInvoiceJour.salesTable().salesLine();
pwAustraliaRetailChild = queryRun.get(tableNum(PWAustraliaRetailChild));
Hope this'll be helpful. Thank You.
Share
Report
All responses (
Answers (
