Microsoft Dynamics AX (Archived)

Create query in X++

(0) Share

Report

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

All responses (3)

Answers (2)

BuBbA 105 on at

Like (0)

Report

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 46,149 on at

Like (0)

Report

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 230,445 Most Valuable Professional on at

Like (0)

Report
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.