Query x++ with multiple datasource and conditional with OR

Hi Dynamic users,

I’m using the Query Framwork in AX2013 R3. I have the following query that already works properly.

Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange qbr1;
QueryRun queryRun;
FromDateTime datelastsync;

datelastsync = DateTimeUtil::addDays(DateTimeUtil::getSystemDateTime(), -1);

query = new Query();
query.allowCrossCompany(true);
query.addCompanyRange(“demo”);

queryBuildDataSource = query.addDataSource(tableNum(CustTable),“CustTable”);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyTable),“DirPartyTable”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyLocation),“DirPartyLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(LogisticsLocation),“LogisticsLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource=queryBuildDataSource.addDataSource(tableNum(LogisticsPostalAddress),“LogisticsPostalAddress”);
queryBuildDataSource.joinMode(JoinMode::OuterJoin);
queryBuildDataSource.relations(true);

The question is: How should I build the Range or filters in the Query() for the following result in SQL:

" … WHERE … AND (CustTable.ModifiedDateTime>@datelastsync OR
DirPartyTable.ModifiedDateTime>@datelastsync OR LogisticsLocation. ModifiedDateTime>@datelastsync OR LogisticsPostalAddress.ModifiedDateTime>@datelastsync ) … "

I have tried in many ways, with .AddRange () and by adding the values (’(% 1% 2>% 3) || … But not return the expected results.

Thank you for your help.

The extended query syntax is one option.

Or you can use the query (without the filter) to build a view and then simply filter the view. It will be just one data source, therefore you can filter it easily.

Hello Martin, thank you for your response. I tried to apply “The extended query” but have failed to deliver expected results.
Here I will show the two ways I’ve tried:

Form 1

Query query;
QueryBuildDataSource queryBuildDataSource;
QueryBuildRange qbr1;
QueryRun queryRun;
UtcDateTime datelastsync;

datelastsync = DateTimeUtil::addDays(DateTimeUtil::getSystemDateTime(), -1);

query = new Query();
query.allowCrossCompany(true);
query.addCompanyRange(“usmf”);

queryBuildDataSource = query.addDataSource(tableNum(CustTable),“CustTable”);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyTable),“DirPartyTable”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyLocation),“DirPartyLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(LogisticsLocation),“LogisticsLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(LogisticsElectronicAddress),“LogisticsElectronicAddress”);
queryBuildDataSource.joinMode(JoinMode::OuterJoin);
queryBuildDataSource.relations(true);

qbr = queryBuildDataSource.addRange(fieldNum(LogisticsPostalAddress,DataAreaId));

qbr.value(strFmt(" ( (%1.%2 > %3) || ( %4.%5 > %3 ) || ( %6.%7 > %3 ) || ( %8.%9 > %3 ) ) ",
"CustTable", fieldstr(CustTable,modifiedDateTime), queryValue(datelastsync),
"DirPartyTable", fieldstr(DirPartyTable,modifiedDateTime),
"LogisticsLocation", fieldstr(LogisticsLocation,modifiedDateTime),
"LogisticsPostalAddress", fieldstr(LogisticsPostalAddress,modifiedDateTime)
));

Form 2

In order to further simplify the condition, I tried using this simple expression, but also failed, the data is not filtered, the query return all rows, and not really understand why. :frowning:

queryBuildDataSource = query.addDataSource(tableNum(CustTable),“CustTable”);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyTable),“DirPartyTable”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(DirPartyLocation),“DirPartyLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(LogisticsLocation),“LogisticsLocation”);
queryBuildDataSource.relations(true);
queryBuildDataSource = queryBuildDataSource.addDataSource(tableNum(LogisticsElectronicAddress),“LogisticsElectronicAddress”);
queryBuildDataSource.joinMode(JoinMode::OuterJoin);
queryBuildDataSource.relations(true);

queryBuildDataSource.addRange(fieldNum(LogisticsPostalAddress,ModifiedDateTime)).value(strFmt(’>%1’, queryValue(datelastsync)));

I appreciate the help you can give me again.

Until next time!!

One obvious bug is in your conversion of datetime values to query ranges. You have to use DateTimeUtil::toStr() with extended query syntax, not queryValue().

What’s more important, you have to learn to debug your queries, so you can actually find what’s wrong.

You wrote an expression to build a SQL query. The next step, obviously, is verifying if it succeeded, i.e. whether the resulting SQL statement is the same as the one you wanted to model. First of all, call toString() on the root datasource to get the pseudo-SQL query used by AX. If the value looks as expected and still it doesn’t work as expected, first review your expectations and then look at the actually SQL query in database. Then you’ll know exactly how your X++ query gets translated to SQL and should understand why the result it what it is.

Hi, Martin, very thanks…

I did this and I already worked well

queryBuildDataSource.addRange(fieldNum(DirPartyTable,ModifiedDateTime)).value(strFmt(’>%1’, datelastsync));

The variable datelastsync so let free without QueryValue, and it worked fine.

Another thing I learned from you was doing the Trace functionality to the query generated by Ax.

query.toString ();

With this I can see exactly the TSQL that will go to the database engine.

This community is very apacionada, thank you and hope to continue counting on you. :slight_smile: