Wanted to outerJoin 2 tables together using X++ Query Help

(0) Share

Report

Posted on by Jjp

// TableC1

Query query = new Query();

QueryBuildDataSource qbdsOne = query.addDataSource(tablenum(TableC1));

// TableC2

QueryBuildDataSource qbdsTableC2 = qbdsOne .addDataSource(tableNum(TableC2));

qbdsTableC2 .addLink(fieldNum(TableC1, B), fieldNum(TableC2, B));

// TableC3

QueryBuildDataSource qbdsTableC3 = qbdsTableC2 .addDataSource(tableNum(TableC3));

qbdsTableC3 .addLink(fieldNum(TableC2, B), fieldNum(qbdsTableC3 , B));

// TableA

QueryBuildDataSource qbds4 = qbdsTableC3 .addDataSource(tablenum(TableA a));

qbds4 .joinMode(JoinMode::OuterJoin);

// TableC1

qbds4 .addLink(fieldNum(TableC1, C1), fieldNum(TableA , field1), qbdsTableC1.name());

// TableC1

qbds4 .addLink(fieldNum(TableC1, C1), fieldNum(TableA , field2), qbdsTableC1.name());

// TableC2

qbds4 .addLink(fieldNum(TableC2, C2), fieldNum(TableA , field1), qbdsTableC2.name());

TableC1 is Okey but TableC2 is error

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Martin Dráb 239,385 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

What's in the Message property of the exception and which line of code is throwing the exception?

By the way, please don't prefix tags with #, do separate words with spaces and use "Microsoft Dynamics AX 2012" as the version for questions about AX 2012. Then you also don't need to tags like AX2012. And the category clearly is "Development / Customization / SDK", not "Supply Chain". I fixed it all for your this time.

Was this reply helpful? Yes No
Blue Wang Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi Jjp,

From your code, there are some errors.

qbdsTableC1 did not declare, you are using qbdsOne, and in the TableC3 part, use addlink(), the parameter should be table name, and you provided qbdsTableC3.

I degugged your code and have no error , get the statement as below, Whether it is what you want?:

SELECT * FROM TableC1(TableC1_1) JOIN * FROM TableC2(TableC2_1) ON TableC1.B = TableC2.B JOIN * FROM TableC3(TableC3_1) ON TableC2.B = TableC3.B OUTER JOIN * FROM TableA(TableA_1) ON TableC1.C1 = TableA.field1 AND TableC1.C1 = TableA.field2 AND TableC2.C2 = TableA.field1

Your code：

// TableC1 Query query = new Query(); QueryBuildDataSource qbdsTableC1 = query.addDataSource(tablenum(TableC1)); // TableC2 QueryBuildDataSource qbdsTableC2 = qbdsTableC1 .addDataSource(tableNum(TableC2)); qbdsTableC2 .addLink(fieldNum(TableC1, B), fieldNum(TableC2, B)); // TableC3 QueryBuildDataSource qbdsTableC3 = qbdsTableC2 .addDataSource(tableNum(TableC3)); qbdsTableC3 .addLink(fieldNum(TableC2, B), fieldNum(TableC3 ,B)); // TableA QueryBuildDataSource qbds4 = qbdsTableC3 .addDataSource(tablenum(TableA)); qbds4 .joinMode(JoinMode::OuterJoin); // TableC1 qbds4 .addLink(fieldNum(TableC1, C1), fieldNum(TableA , field1), qbdsTableC1.name()); // TableC1 qbds4 .addLink(fieldNum(TableC1, C1), fieldNum(TableA , field2), qbdsTableC1.name()); // TableC2 qbds4 .addLink(fieldNum(TableC2, C2), fieldNum(TableA , field1), qbdsTableC2.name());

Was this reply helpful? Yes No
hai ming 20 on at

Like (0)

Report
Copy link

Link copied!

是不是qbdsTableC1不存在呢？

Was this reply helpful? Yes No
Martin Dráb 239,385 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

hai ming, please use English in this forum. Thank you.

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (0)

Report
Copy link

Link copied!

qbdsTableC3 .addLink(fieldNum(TableC2, B), fieldNum(qbdsTableC3 , B));

to

qbdsTableC3 .addLink(fieldNum(TableC2, B), fieldNum(TableC3, B));

qbdsTableC3 --> TableC3 (Wang is saying You probaly dont have a table which is named qbdsTableC3)

And You did began with qbdsOne but at somepoint used qbdsTableC1which is not declared before

Was this reply helpful? Yes No