AnsweredAssumed Answered

Finding Master Program for a set of Projects

Question asked by bfarquhar on Mar 6, 2012
Latest reply on Nov 10, 2017 by joannm
I'm hoping someone has done this before and can provide some SQL or help figure out how I can get the Master (very hieghest) Program for a set of projects. This is to add "Master Program field to a portlet that returns project data.

I have the NSQL for a portlet that returns a rows of data for a project. If a project is attached to more than 1 program it will return a row for each program it is attached to.

With that said I now need to identify the "Master" program at the top of each hierarchy. I wrote some SQL that can do that. It uses the niku.INV_TRAVERSE_TO_TOP_FCT. This works just fine when I use a single project ID like this....

Select child_id

from niku.INV_TRAVERSE_TO_TOP_FCT(5022667)

where parent_id is null

and child_id not in

(select distinct child_id

from niku.INV_TRAVERSE_TO_TOP_FCT(5022667)

where parent_id is not null)

Question - is it possible to feed this function a set of ID's ? Like from a sub select that returns a set of ID's ?

IF so I cant seem to figure out the syntax.

IF not.... Does anyone know how I could achieve the same result.... feed in a set of project ID's and get the top most program from the heirarchy for each ID ? Is there another function maybe that does this ?

FYI.... I use MSQL and the niku.INV_TRAVERSE_TO_TOP_FCT is found int the Table-valued Functions

Any help people could provide would be much appreciated.