Help wanted to innerJoin 3 tables together using X++ Query

Dynamics 365 for Finance and Operations/AX Developer Forum
1

Hello everyone,

I found something unexpected when I was using the X++ query classes.

I have 2 jobs below

-------Job 1–

static void Job_01(Args _args)

{

Query query;

QueryBuildDataSource salesTableDS;

QueryBuildDataSource salesLineDS;

QueryBuildDataSource custTableDS;

Queryrun qr;

int counter;

query = new Query();

salesTableDS = query.addDataSource(tableNum(SalesTable));

salesLineDS = salesTableDS.addDataSource(tableNum(SalesLine));

custTableDS = salesTableDS.addDataSource(tableNum(custTable));

salesLineDS.relations(true);

custTableDS.relations(false);

custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

custTableDS.joinMode(JoinMode::InnerJoin);

qr = new QueryRun(query);

while(qr.next())

{

info(“run here” + int2str(counter));

counter = counter+1;

}

info(query.xml());

}

----Job 02-------Reshuffle the table sequence–

static void Job_02(Args _args)

{

Query query;

QueryBuildDataSource salesTableDS;

QueryBuildDataSource salesLineDS;

//VP-1008 KL_NS

QueryBuildDataSource custTableDS;

//VP-1008 KL_NE

Queryrun qr;

int counter;

query = new Query();

//VP-1008 KL_NS

salesLineDS = query.addDataSource(tableNum(SalesLine));

salesTableDS = salesLineDS.addDataSource(tableNum(SalesTable));

salesTableDS.relations(true);

custTableDS = salesTableDS.addDataSource(tableNum(custTable));

// and some additional code to define relations

custTableDS.relations(false);

custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));

custTableDS.joinMode(JoinMode::InnerJoin);

qr = new QueryRun(query);

while(qr.next())

{

info(“run here” + int2str(counter));

counter = counter+1;

}

info(query.xml());

}

========

What I want to do, if using pure SQL towards SQL Server 2008, is as follows:

FROM SALESLINE T1

inner JOIN SALESTABLE T2

inner JOIN CUSTTABLE T3

WHERE

(T1.SALESID=T2.SALESID))

AND (T2.CUSTACCOUNT=T3.ACCOUNTNUM))

==========

The Job_01 does NOT work, only job_02 works, which is totally beyond my expectation.

Can someone shed some light into this problem? I mean, this is a foundation that we build complicated queries. If we are confronted with the problem to build 7 or 8 tables together, and there is a central table(like the SalesTable in this case) that links to a couple table, how can we organize the code, if we cannot do Job_01?

I attached a picture to illustrate my concern.

Any ideas greatly appreciated!

Kwen

2

My problem description PDF is at

2543.Question_linking multiple table through Query API.pdf (8.33 KB)

3

All joins should be “InnerJoin”, I compare the XML for both Jobs’ output, they are Different, so, there must be something specific to the QueryBuildDataSource structure. And I really want to know how I can use “SalesTable” as the starting point, as it could be the center table for future even more complicated query work.

Thank you very much, guys!

Kwen

4

Could you provide a better definition of “doesn’t work”?

Please set FetchMode to One2One in the first job, get query strings for both queries (by calling toString() on the root data source) and compare them. That’s what you need to do to debug things instead of just wondering about what’s going on.

5

Thank you for your attention, Martin! OK, I am showing what I see through this video, I guess it will save us time to see what the problem is. http://screencast.com/t/SgffyA2Tfob

The bottom line is, the Job_01 is having duplicates records returned and the SQL is not combining the 3 table into a single SQL clause. While everything for Job_02 is doing what I wanted. The desired effect should perform the same query like what the following SQL is doing, using inner join to combine 3 tables together and hit the database with a single SQL clasue:

SELECT * FROM SALESLINE T1

inner JOIN SALESTABLE T2

inner JOIN CUSTTABLE T3

WHERE

(T1.SALESID=T2.SALESID))

AND (T2.CUSTACCOUNT=T3.ACCOUNTNUM))

Thank you,

Kwen

6

Please copy and paste this URL and see the problem: http://screencast.com/t/SgffyA2Tfob

7

If we debug, we can see that the SQL for Job_01 is split and it has multiple SQL clauses hitting the database, I am lost why it is behaving like this. And this is fundamental to assemble the complicated Query in future, and AX world should have something in place to handle, but so far, I am not on the right track to convince myself yet.

Any advice greatly appreciated.

Kwen

8

I see you haven’t followed my advice fully, so let me to do the job for you this time:

Job_01:

Query                   query = new Query();
QueryBuildDataSource    salesTableDS;
QueryBuildDataSource    salesLineDS;
QueryBuildDataSource    custTableDS;
   
salesTableDS    = query.addDataSource(tableNum(SalesTable));
    
salesLineDS     = salesTableDS.addDataSource(tableNum(SalesLine));
salesLineDS.relations(true);
salesLineDS.fetchMode(QueryFetchMode::One2One);
    
custTableDS     = salesTableDS.addDataSource(tableNum(CustTable));    
custTableDS.relations(false);    
custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum));
custTableDS.fetchMode(QueryFetchMode::One2One);

info(salesTableDS.toString());

Output:

SELECT * FROM SalesTable(SalesTable_1)
JOIN * FROM SalesLine(SalesLine_1)
ON SalesTable.SalesId = SalesLine.SalesId
JOIN * FROM CustTable(CustTable_1)
ON SalesTable.CustAccount = CustTable.AccountNum

Job_02

Query                   query = new Query();
QueryBuildDataSource    salesTableDS;
QueryBuildDataSource    salesLineDS; 
QueryBuildDataSource    custTableDS; 

salesLineDS = query.addDataSource(tableNum(SalesLine));
    
salesTableDS = salesLineDS.addDataSource(tableNum(SalesTable));
salesTableDS.relations(true); 

custTableDS = salesTableDS.addDataSource(tableNum(CustTable));
custTableDS.relations(false);
custTableDS.addLink(fieldNum(SalesTable, CustAccount), fieldNum(CustTable, AccountNum)); 
 
info(salesLineDS.toString());

Output:

SELECT * FROM SalesLine(SalesLine_1)
JOIN * FROM SalesTable(SalesTable_1)
ON SalesLine.SalesId = SalesTable.SalesId
JOIN * FROM CustTable(CustTable_1)
ON SalesTable.CustAccount = CustTable.AccountNum

Doesn’t it make things cleaner and clearer?

9

Thank you very much, Martin!

The key line you listed out as follows is the key.

setFetchMode(QueryFetchMode::One2One)

Problem is solved.