web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Range in joined query ...
Microsoft Dynamics AX (Archived)

Range in joined query not exists in SQL

(0) ShareShare
ReportReport
Posted on by Jan M. 3,325

Hi

The rangeStr does never reach SQL, think the reason is, that the field RouteJobs exists in table ProdTable and qbs is from ProdBOM. Right?

Query                   query = new Query();
QueryRun                queryRun;
QueryBuildDataSource    qbsProdTable;
QueryBuildDataSource    qbsProdBOM;
str                     rangeStr;
rangeStr = strFmt('(%1 != "") && (%2 != %3) || (%1 == "")',
			fieldStr(ProdBOM, InventRefId),
			fieldStr(ProdTable, RouteJobs),
			enum2int(NoYes::No));

query.literals(true);
qbsProdTable = query.addDataSource(tablenum(ProdTable));
qbsProdBOM = qbsProdTable.addDataSource(tablenum(ProdBOM));
qbsProdBOM.addLink(fieldnum(ProdTable,ProdId), fieldnum(ProdBOM,ProdId));
qbsProdBOM.addRange(fieldnum(ProdBOM,DataAreaId)).value(rangeStr);
    
queryRun = new QueryRun(query);
while (queryRun.next())
{
    info('stop for debug');
}

I need the InventRefId from ProdBOM, so how the range can be defined?

Thanks for help.

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    The first problem I could spot is that the whole advanced query range must be encapsulated between parentheses, try fixing that first. They are also like that in the documentation:

    https://msdn.microsoft.com/en-us/library/aa893981.aspx

    The second problem is that you are not defining Table.Field for value comparison while you are using 2 tables.

    Also you are setting the advanced range on DataAreaId, maybe try InventRefId instead, though that should not affect it, do it just in case for better visuals.

  • Jan M. Profile Picture
    Jan M. 3,325 on at

    Hi Vilmos

    Tried this allready without change to the result.

    rangeStr = strFmt('((ProdBOM.%1 != "") && (ProdTable.%2 != %3) || (ProdBOM.%1 == ""))',

    Putting the range on other fields also used in the query is not recommended, however it did not change the result.

    SQL Where

    FROM PRODTABLE T1 CROSS JOIN PRODBOM T2 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'ch10')) AND ((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'ch10')) ORDER BY T1.PRODID


    If a field from ProdBOM ist uses like BackorderStatus, SQL range is correct

    FROM PRODTABLE T1 CROSS JOIN PRODBOM T2 WHERE ((T1.PARTITION=5637144576) AND (T1.DATAAREAID=N'ch10')) AND (((T2.PARTITION=5637144576) AND (T2.DATAAREAID=N'ch10')) AND ((((T2.INVENTREFID<>'') AND (T2.BACKORDERSTATUS<>0)) OR (T2.INVENTREFID='')) AND (T1.PRODID=T2.PRODID))) ORDER BY T1.PRODID


    Other ideas?

  • Verified answer
    Jan M. Profile Picture
    Jan M. 3,325 on at

    Finaly the solution: AX assigns ProdTable_1 to ProdTable, so a name for qbs should be given to overwrite the standard.  This will be used as prefix.

    qbsProdTable = query.addDataSource(tablenum(ProdTable), "pTable");
    rangeStr = strFmt('(((%1 != "") && (pTable.%2 != %3)) || (%1 == ""))',
    Or you can get the name by qbsProdTable.name();, may more useful for uge queries.
  • Vilmos Kintera Profile Picture
    Vilmos Kintera 46,149 on at

    So you have implemented it similarly to what the example I have linked is doing. Naming the QueryBuildDataSource, and retrieving the name, as how it should have been. Kindly mark that as the correct answer as well.

  • Jan M. Profile Picture
    Jan M. 3,325 on at

    You mentioned table.field but not how, if also inside the article "customer" is named, how AX dealt with the name was not clear. For example just tableStr() would not help without naming it.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 16

#2
GiacomoRovai Profile Picture

GiacomoRovai 4

#3
Douglas Noel Profile Picture

Douglas Noel 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans