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
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 Martin and Jonathan,
Yes, that was my thinking as well. I just asked in case if some solution exists for such case.
Thanks.
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,
Actaully idea is that I have table with following values:
ParentId | ChildId |
- | V1 |
V1 | V2 |
V2 | V3 |
V2 | V4 |
So, I need to have AOT query, who can find V3 and V4 as those are lowest values (don't have childs) in this hierachy .
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.
André Arnaud de Cal... 291,391 Super User 2024 Season 2
Martin Dráb 230,445 Most Valuable Professional
nmaenpaa 101,156