sivasairam

SQL Server query that shows Program-project hierarchy upto n levels

Discussion created by sivasairam on Sep 21, 2010
Latest reply on Sep 21, 2010 by Chris_Hackett
Hi All

I thought this query will be useful for people with SQL Server as their Clarity databse.

Below query shows program-project-sub1-sub2----subn relationship

WITH n(child_id,level,sortkey) AS
(SELECT child_id, 1 as level,
CAST(child_id as varchar(8000)) as SortKey
FROM INV_HIERARCHIES hierarchy, inv_projects invp
where hierarchy.parent_id=invp.prid
and invp.is_program=1
and invp.is_template=0
UNION ALL
SELECT parent.child_id, Level+1,
CAST(n.SortKey as varchar(8000)) + CAST(parent.id as varchar(8000))
FROM INV_HIERARCHIES as parent, n
WHERE n.child_id=parent.parent_id)
SELECT child_id,level,name from n
inner join niku.inv_investments inv
on n.child_id=inv.id
order by sortkey



Thanks
Siva

Outcomes