Ax AOT Query for Hierachy

(0) Share

Report

Posted on by MarisR

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

All responses (6)

Answers (3)

Verified answer

MarisR 545 on at

Like (0)

Report

RE: Ax AOT Query for Hierachy

Hi Martin and Jonathan,

Yes, that was my thinking as well. I just asked in case if some solution exists for such case.

Thanks.
Verified answer

Jonathan Halland 11,304 on at

Like (1)

Report

RE: Ax AOT Query for Hierachy

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
Verified answer

Martin Dráb 230,445 Most Valuable Professional on at

Like (1)

Report

RE: Ax AOT Query for Hierachy

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.
MarisR 545 on at

Like (0)

Report

RE: Ax AOT Query for Hierachy

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 .
Douglas Noel 3,905 on at

Like (0)

Report

RE: Ax AOT Query for 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
Jonathancm 416 on at

Like (0)

Report

RE: Ax AOT Query for Hierachy

You mean like an "order by" clause?

Regards.