Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Unanswered

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

Posted on by 40

// 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());pastedimage1609118812789v2.png

TableC1 is Okey but TableC2 is error

  • ergun sahin Profile Picture
    ergun sahin 8,812 Super User on at
    RE: Wanted to outerJoin 2 tables together using X++ Query Help

    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

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: Wanted to outerJoin 2 tables together using X++ Query Help

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

  • hai ming Profile Picture
    hai ming 20 on at
    RE: Wanted to outerJoin 2 tables together using X++ Query Help

    是不是qbdsTableC1不存在呢?

  • Blue Wang Profile Picture
    Blue Wang on at
    RE: Wanted to outerJoin 2 tables together using X++ Query Help

    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());

  • Martin Dráb Profile Picture
    Martin Dráb 225,490 Super User on at
    RE: Wanted to outerJoin 2 tables together using X++ Query Help

    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.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,696 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,490 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans