Union in AX 2012

(0) Share

Report

Posted on by Community Member

Hi All,

I have an issue making union, I have the table A that has 2 different joins with the other tables; I want to make the same query in the table 2 but the difference is the one join is "NotExists", when I try to union the 2 queries with this sentence:

qr.queryType(QueryType::Union);

and I run the form, the infolog shows me this error:

The join mode in union query is invalid. For a join query, the join mode of the second level data source must be either exists or notexists.

I read in other forums, the UNION doesn't work with joins. Someone can help me to solve the issue using UNION or similar?

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (0)

Suggested answer

Mea_ 60,284 on at

Like (0)

Report

Hi Eduardo Cabrera,

You can always create AOT view for 2 joined tables and then use this view as a datasource in union.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

But you can't use the type NoExists in the view

Was this reply helpful? Yes No
Suggested answer

Mea_ 60,284 on at

Like (0)

Report

Ok let me give you additional details:

A) Create a query to join 2 tables using not exists join.

B) Create a view base on query from A.

C) Create new query where you will union view previously created (B) with table you want.

Was this reply helpful? Yes No
Tommy Skaue Moderator on at

Like (0)

Report

Hi Eduardo

ievgen is right. You should create multiple Levels using a combination of queries and views to best solve this. Unions can be tricky, and sometimes they even cause the client to crash while working with them.

So just as ievgen explains, you create two results, using the Query1 on table A as datasource on a view (View1) and Query2 on table A as datasource on a second view (View2), then you use a Third Query of type Union to combine the results of View1 and View2. Union is basically just combining two resultsets into one bigger resultset, so make sure the two views have the same columns and the columns in same order (as a good practice).

:-)

Was this reply helpful? Yes No