Find ReportingManager

I am having EmployeeTable table with two fields EmpId,Boss with records as follows

EmployeeTable.

EmpId ReportingManager


A

B A

C B

By single select statement I have to find the how to find the RemportingManager of RemportingManager of C ie

the reporting manager of C is B and his reporting manager is A.

i wrote “select firstonly EmployeeTable” :slight_smile: to get “A” which is not correct

Hi,

I don’t believe that would be possible to write something like this in a one single AX SQL Statement. In theory you would have to call the search of the next Reporting Manager recursively. If you would create a findReportingManager() method that finds direct manager for employee C and the method would find his manager B, then you could call the same method from within itself once again in order to find who is (and whether there is) a manager for the employee B. Of course, in order not to get into an infinite loop you would have to stop the recursion as soon as you have found someone on the top of the top for whom you cannot find any reporting manager.

Here is an example of how you could achieve this in native SQL, but yeahhhhh… this won’t help you with AX in any way :slight_smile:

http://www.dbforums.com/db2/1610901-how-write-recursive-sql-join-i-am-not-sure-even-posible.html

Janis.

Hi Janis Cehovs,

We can do it by creating static find() method to find the parent as you said above. But I think we do it by self Join which is available in SQL statements, but I m unable to do it in AX Query. :slight_smile:

Wouldn’t the join with a same table work only one level down? What if you have employee structure like A → B → C → D ? Are you confident that you will always have just one middle Reporting manager and not more?