Project and Parent SQL

[Legacy User]

I'm trying to write some sql that lists the id, project name, and then the name and id of the parent directly above it in the hierarchy. The project above it in the hierarchy must also not be a program, which i've figured out.

Infact, I've managed to bring back parent information but as soon as the parent is then below another project in the hierarchy, it brings back the proejct at the top of the hierarchy, rather than the project directly above it in the hierarchy.

I also want there to only be row in my results for each project - so I don't want multiple rows if the project is directly under multiple projects.

Does anyone know of anything available for this?

Ben

[Deactivated User]

There have been some threads on the hierarcy/parent-child.
LIke
2295820
Which table(s) do you query?
There is also a Tuestday's tip on
Inv_Flat_Hierarchies

Martti K.

[Owen_R]

Have you tried using the two hierarchy based table value functions: INV_TRAVERSE_TO_BOTTOM_FCT and INV_TRAVERSE_TO_TOP_FCT ( I think I've posted threads about them here before).

These loop through the structure, either up from the bottom or down from the top, based on a parent or child parameter and break out the records showing the parent/child relationship and level of the hierarchy.

You can then do something like this - which will give you all records beneath a parent:

declare @parent_id as integer

select
parent.unique_name as parent_code,
parent.name as parent,
child.unique_name as child_code,
child.name as child,
hier.lvl as hierarchy_level

from

srm_projects parent inner join 
niku.inv_traverse_to_bottom_fct(@parent_id) hier on hier.parent_id = parent.id inner join
srm_projects child on child.id = hier.child_id

Owen