marlon.mckenzie

Investment Hierarchy (Program-Project-Subproject)

Discussion created by marlon.mckenzie on Mar 16, 2011
Hi All

I am attempting to created a query that constructs the hierarchy of all the investments in my system and am running into an issue:

1. When I filter by a specific investment, I am trying to show not only that investments path but not the results/path of any child/grandchild investment.

Query:
SELECT INV.ID, INV.NAME, SYS_CONNECT_BY_PATH(INV.NAME,'/') INV_PATH
FROM INV_INVESTMENTS INV,
(SELECT IH.CHILD_ID, IH.PARENT_ID
FROM INV_HIERARCHIES IH
)CHPA
WHERE CHPA.CHILD_ID(+) = INV.ID
AND INV.ID = 5001000
START WITH CHPA.PARENT_ID is NULL
CONNECT BY PRIOR CHPA.CHILD_ID = CHPA.PARENT_ID

Results:
5000000 ProgramA /ProgramA

Desired Results:
5000000 ProgramA /ProgramA
5000001 ProjectA /ProgramA/ProjectA
5000002 SubProjectA /ProgramA/ProjectA\SubProjectA

Is this possible?

Thanks,
Marlon

Outcomes