Difference between Join Mode and Relation in Query Classes AX2012

(0) Share

Report

Posted on by vaasu

415

Hi All,

I am very new in Ax.

Can anybody explain briefly about joinmode and relation in query.

Thanks,

Srinivas

*This post is locked for comments

I have the same question (0)

All responses (1)

Answers (0)

Suggested answer

Brandon Wiese 17,788 on at

Like (3)

Report

RE: Difference between Join Mode and Relation in Query Classes AX2012

When setting .relations(true) on QueryBuildDataSource, the join relationship between the current table and its immediate parent is added automatically from the available relations set in the AOT. It basically says "use the standard relationship". Note that there can be two relations, i.e. SalesTable to CustTable using either CustAccount or InvoiceAccount, where you might want .relations(false) so that you can be certain which one is used by adding it yourself, or you can set .relations(true) and there may not be any relation at all to use, and then you'll have to add it yourself anyway. It's identical to setting Relations to true in the property page when building a query in the AOT.

The .joinMode() is used to tell AX what kind of JOIN to use. For example, ExistsJoin would generate a WHERE EXISTS clause instead of an INNER JOIN for the data source, and NoExistsJoin, the opposite. Of course there's OuterJoin, which creates a LEFT JOIN, and the standard InnerJoin options.

The one that confuses most people is .fetchMode() of One2One versus One2Many. I've found that there are cases where having more than one One2Many fetch mode from the same data source to child data sources causes the engine to fail to build the query correctly, and in a cause like that where you actually have multiple 1:n relationships in the data, the number of records can explode inadvertently. I believe the engine can sometimes break the query into multiple pieces in order to return the results you need, but I've never found any documentation on how this works even though you can see it happening with SQL Server Profiler. In any case, some restructuring of the query is usually possible, and a n:1 structure (for which you would tell AX to use One2One) tends to be a safer build sequence.

Was this reply helpful? Yes No