Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Create query in X++
Microsoft Dynamics AX (Archived)

Create query in X++

(0) ShareShare
ReportReport
Posted on by BuBbA 105

Hi guys,

I am trying to build a query in X++ but i can't seem to do it right.

Example:

Table 1

Table 2 => Has a relation with Table 1

Table 3 => Has a relation with Table 1

My code:

Query query = new Query();

QueryBuildDatasource qbds1, qbds2, qbds3;

qbds1 = query.addDatasource(tableNum(Table1));

qbds2 = qbds1.addDatasource(tableNum(Table2));

qbds2.relation(true);

qbds3 = qbds1.addDatasource(tableNum(Table3));

qbds3.relations(true);

Now my problem:

Adding Table1 => Ok, in debugger i can see query object as "Query object 2c70e208: SELECT * FROM Table1(Table1_1)"

Adding Table2 => Ok, in debugger i can see query object as "Query object 2c70e208: SELECT * FROM Table1(Table1_1) JOIN * FROM Table2(Table2_1) ON Table1.Id = Table2.Id"

Adding Table3 => NOK, the query object looks to be broken and in the debugger it gets the following value "Query object 2c70e208"

When i write the query in a select statement, in SQL or build it in a AOT query there is no problem and i get the results correctly.

Does anyone have an idea what's wrong?

*This post is locked for comments

  • BuBbA Profile Picture
    BuBbA 105 on at
    RE: Create query in X++

    Thanks guys, that was very quick! Adding the fetch mode to the query solved it for me!

  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at
    RE: Create query in X++

    Try setting .fetchMode(QueryFetchMode::One2One);

    Some additional details:

    [View:http://dynamicsaxhints.blogspot.hu/2015/11/query-datasource-fetchmode-property.html:750:50]

    [View:http://www.to-increase.com/ax-fetch-mode/:750:50]

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 230,214 Most Valuable Professional on at
    RE: Create query in X++

    You should distinguish between what you see in debugger and whether the query works (which should still be the case).

    In general, QueryBuildDataSource.toString() (which is also what you see in debugger) doesn't work very well with queries with several branches.

    You can change the structure of your query:

    From:
T1 -> T2
   -> T2
    
To something like:
T2 -> T1 -> T3

    Alternatively, setting fetch mode to One2One (on both child data sources) should fix it too.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans