You’re offline. This is a read only version of the page.
545
Hello,
An example, table with two fields ParentId and ChildId. Is it possible to create AOT query and based on range value for ParentId to find lowest ChildId in hierarchy?
Thanks.
*This post is locked for comments
Hi AXUSer.
I don't believe this is possible via the Queries or X++ select statements in AX. There is no particular handling of this recursion or advanced joins etc...
I would suggest you flatten your heirarchy into a temp table and then do a select as needed or simply loop through your results
http://stackoverflow.com/questions/13992244/how-to-select-lowest-level-in-hierarchy-from-table
Such a data model can't be easily queried by usual SQL statements; you would have to do recursive queries. Because this is generally bad for performance, it's often better to change the data model. For example, if you maintain the hierarchy level on each line, getting what you want in a single query will become trivial.
Hi AxUser,
this should be possible using the minOf() grouping funtion. (Assumed the 'lowest' is the one with the lowest 'order relation' on the field type - string, real, ...)
Create a query: DynamicFields=no
Add the ParenID field and add the Min Field ChildId
this will create something like 'select ParentID, min(ChieldID) from table group by [partition,datatarea]ParentID
But I never checked this.
So theoretically you should be able to add a (QueryBuild)Range on the parentField to the QueryBuidDataSource 'table'
regards
Douglas
You mean like an "order by" clause?
Regards.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,113
Super User 2024 Season 2
#2
Martin Dráb
229,918
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (