Here is one that I use.
In the with clause:
CHILD_INVESTMENTS is all investments that have a parent.
INVEST_TREE pulls the top level investments and a recursive query to pull their children from CHILD_INVESTMENTS for each top level investment.
INVEST_DISTINCT just makes sure we only have one row for each parent child relationship (Not sure this is really needed but habits are hard to break).
INVEST_NSQL is the values we are going to use for our NSQL. It is in this query you would join to your custom field tables. I am pulling off fields in ODF_CA_PROJECT and CMN_LOOKUPS_V
SELECT @SELECT:DIM:USER_DEF:IMPLIED:X:X.PROJECTID:PROJECTID@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRDESC:PRDESC@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@ ,
@SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.LICYPHASE:LICYPHASE@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.COSTIND:COSTIND@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PERFIND:PERFIND@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.RISKIND:RISKIND@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.SCHEDIND:SCHEDIND@ ,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.IPT:IPT@
FROM
( WITH CHILD_INVESTMENTS AS
( SELECT DISTINCT inv.ID,
inh.PARENT_ID,
inh.CHILD_ID,
inp.PARENT_ID AS HG_HAS_CHILDREN,
inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh
ON inv.ID = inh.CHILD_ID
AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp
ON inv.ID = inp.PARENT_ID
WHERE inv.IS_ACTIVE = 1
),
INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) AS
(SELECT ID,
NAME,
PARENT_ID,
0 AS DEPTH,
ID AS ROOT_ID,
HG_HAS_CHILDREN
FROM CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
AND NAME = 'Enterprise Infrastructure'
UNION ALL
SELECT ci.ID,
ci.NAME,
ci.PARENT_ID,
DEPTH + 1,
ROOT_ID,
ci.HG_HAS_CHILDREN
FROM CHILD_INVESTMENTS ci
INNER JOIN INVEST_TREE iv
ON ci.PARENT_ID = iv.ID
),
INVEST_DISTINCT AS
( SELECT DISTINCT ID,
NAME,
PARENT_ID,
DEPTH,
ROOT_ID,
HG_HAS_CHILDREN
FROM INVEST_TREE
),
INVEST_NSQL AS
(SELECT DISTINCT inv.ID PROJECTID ,
iv.DEPTH ,
iv.HG_HAS_CHILDREN ,
iv.PARENT_ID ,
inv.NAME PRNAME ,
inv.description prdesc ,
inv.SCHEDULE_FINISH PRFINISH ,
inv.SCHEDULE_START PRSTART ,
inv.IS_ACTIVE ACTIVE ,
lkp0.NAME LICYPHASE ,
odfp.MHS_COST_VAR*100 COSTIND ,
odfp.MHS_PERFORMANCE_HLTH PERFIND ,
odfp.MHS_RISKINDICATOR RISKIND ,
odfp.MHS_SCHED_VAR*100 SCHEDIND ,
lkp1.NAME IPT
FROM INVEST_DISTINCT iv
INNER JOIN INV_INVESTMENTS inv
ON iv.ID = inv.id
INNER JOIN ODF_CA_PROJECT odfp
ON iv.ID = odfp.ID
LEFT OUTER JOIN CMN_LOOKUPS_V lkp0
ON lkp0.LOOKUP_CODE = odfp.MHS_LIFECYCLE_PHASE
AND lkp0.LANGUAGE_CODE = 'en'
AND lkp0.LOOKUP_TYPE = 'MHS_LIFECYCLE_PHASE'
LEFT OUTER JOIN CMN_LOOKUPS_V lkp1
ON lkp1.LOOKUP_CODE = odfp.MHS_IPT
AND lkp1.LANGUAGE_CODE = 'en'
AND lkp1.LOOKUP_TYPE = 'MHS_ASSCTD_IPT'
)
SELECT PROJECTID,
HG_HAS_CHILDREN ,
PARENT_ID ,
PRNAME ,
PRDESC ,
PRFINISH ,
PRSTART ,
ACTIVE ,
LICYPHASE ,
COSTIND ,
PERFIND ,
RISKIND ,
SCHEDIND ,
IPT
FROM INVEST_NSQL insql
WHERE Depth = NVL(@WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@ , 1)
AND @WHERE:SECURITY:PROJECT:PROJECTID@
UNION
SELECT PROJECTID,
HG_HAS_CHILDREN ,
PARENT_ID ,
PRNAME ,
PRDESC ,
PRFINISH ,
PRSTART ,
ACTIVE ,
LICYPHASE ,
COSTIND ,
PERFIND ,
RISKIND ,
SCHEDIND ,
IPT
FROM INVEST_NSQL insql
WHERE PARENT_ID = @WHERE:PARAM:USER_DEF:STRING:HG_ROW_ID@
AND @WHERE:SECURITY:PROJECT:PROJECTID@
ORDER BY PRNAME
) X
WHERE @FILTER@
V/r,
Gene