Using a Union Query in a Form - 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.

(0) Share

Report

Posted on by Community Member

Hi all,

I have a form with multiple datasources. One of the datasources is NOT joined to any of the other datasources. This datasource is called CarTable_DS. The records for this datasource are loaded through code in the executeQuery method of the datasource.

Basically, I have a centralized method which constructs an X++ query object with all the ranges and returns the query to the caller. Apart from being used in the form, this method is used in a lot of other places and that is why I decided to centralize it. The method is called constructShowAllCarTypesQuery().

public Query constructShowAllCarTypesQuery()
{
Query qFirstTypeFrom;
Query qSecondTypeFrom;
Query qThirdTypeFrom;
QueryBuildDataSource qbdsFirstTypeFrom;
QueryBuildDataSource qbdsSecondTypeFrom;
QueryBuildDataSource qbdsThirdTypeFrom;

Query qUnion;
QueryBuildDataSource qbdsFirstTypeTo;
QueryBuildDataSource qbdsSecondTypeTo;
QueryBuildDataSource qbdsThirdTypeTo;
;

// Get all the datasources
qFirstTypeFrom = this.constructFirstTypeQuery();
qbdsFirstTypeFrom = qFirstTypeFrom.dataSourceTable(tableNum(CarsTable));

qSecondTypeFrom = this.constructSecondTypeQuery();
qbdsSecondTypeFrom = qSecondTypeFrom.dataSourceTable(tableNum(CarsTable));

qThirdTypeFrom = this.constructThirdTypeQuery();
qbdsThirdTypeFrom = qThirdTypeFrom.dataSourceTable(tableNum(CarsTable));

// Construct the query
qUnion = new Query();
qUnion.queryType(QueryType::Union);

qbdsFirstTypeTo = qUnion.addDataSource(tableNum(CarsTable), identifierStr("CarsTable_FirstType"));
DFMUtils::copyRanges(qbdsFirstTypeTo, qbdsFirstTypeFrom);
qbdsFirstTypeTo.fields().dynamic(false);
qbdsFirstTypeTo.fields().clearFieldList();
qbdsFirstTypeTo.fields().addField(fieldNum(CarsTable, CarId));

qbdsSecondTypeTo = qUnion.addDataSource(tableNum(CarsTable), identifierStr("CarsTable_SecondType"), UnionType::Union);
DFMUtils::copyRanges(qbdsSecondTypeTo, qbdsSecondTypeFrom);
qbdsSecondTypeTo.fields().dynamic(false);
qbdsSecondTypeTo.fields().clearFieldList();
qbdsSecondTypeTo.fields().addField(fieldNum(CarsTable, CarId));

qbdsThirdTypeTo = qUnion.addDataSource(tableNum(CarsTable), identifierStr("CarsTable_ThirdType"), UnionType::Union);
DFMUtils::copyRanges(qbdsThirdTypeTo, qbdsThirdTypeFrom);
qbdsThirdTypeTo.fields().dynamic(false);
qbdsThirdTypeTo.fields().clearFieldList();
qbdsThirdTypeTo.fields().addField(fieldNum(CarsTable, CarId));

return qUnion;
}

Now, in the executeQuery method on the form, I am doing this:

public void executeQuery()
{
Query q;
;

q = CarsTable.constructShowAllCarTypesQuery();
CarsTable_DS.query(q);

super();
}

However, when I now open the form, I am getting 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.

How can I solve this problem please? I tried to put the join mode in the constructAllCarTypes method to exists join for the second and third datasources to no avail. Furthermore, I wasn't able to find anything for this error on the internet.

P.S. Important Info. When running the query through a job, it works fine and returns the expected results. It is ONLY giving problems when trying to use it in a form in the way that I described above.

Thank you in advance!

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Verified answer

Denis Macchinetti 16,444 on at

Like (0)

Report
Copy link

Link copied!

Hi

Try to create three views instead and use them in a query.

Finally, use the query on the form or create a new view with the query as the datasource. Finally use the View on the form.

I don't know the real reason, but in this way should work.

Take a look to the standard query InventValueReportSubContUnionAll

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Denis,

Thank you so much for your reply. Unfortunately, I can't use an AOT query since the form has a load of other datasources. So there is no way to work using the method that I am trying to use?

Was this reply helpful? Yes No
Tommy Skaue Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

I must say I agree somewhat with Denis.

You write you cannot use a query on the form, because it already has other datasources. I can understand that you do not want to completely rewrite the datasource for the form itself. Makes perfect sense.

However, you could try to redesign your own model of tables into a combination of queries and views, and then instead of using tables in your X++ method, you refer to a predefined set of views. It sounds easy on face value, but it will probably require you to wrap your head around using views and queries, and perhaps nest them.

Also, Unions are quite painful to use in AX, in my experience. They are very strict on how you can apply them, and I've also experienced crash when building them. I haven't tried to union two sources where one (or both) of them have a second level join, but I guess that is just one of those limitations one does have in X++ (yet not in SQL). This is where views and queries in combination comes into play.

Just my two cents. :-)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Denis and Tommy. Thank you so much for your replies.

What I ended up doing is to create a temporary table and I added that as a datasource to the form. In the executeQuery of the temp table datasource, I then fill the temp table by looping over the union query. In this way, I was able to solve my problem.

Thank you again for your replies. I appreciate your time and dedication.

Was this reply helpful? Yes No
Tommy Skaue Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Sounds pragmatic, but will it perform? Consider it needs that extra step of populating a temporary table (In memory or tempdb?). You will need to consider the performance of your implementation. Users quickly lose patience. :-)

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Hi Tommy. The temp table is of type inMemory. You are right. I didn't think much about performance. Will try to see how I can improve it before it gets to be a problem. Thank you so much.

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report
Copy link

Link copied!

Hi Anthony

Please, remember to tick as verified the answers that helped you.

Was this reply helpful? Yes No