In response to a request on the forums, here's a SQL query that uses common table expressions (CTE) to visually return the program execution chain hierarchy.
(something like this)
00000000 - ProgramA
00000001 - ProgramB
0000004 - ProgramE
00000002 - ProgramC
00000003 - ProgramD
Here you can see that ProgramE is dependent upon ProgramB, which is dependent upon Program A.
ProgramC&D have no dependents.
NOTE: This only works in SQL 2005 or higher since CTE was introduced with 2005.
WITH ProgramHierarchy(PackageID, ProgramName, ParentPackageID, ParentProgramName,[Path],[Level]) AS
(
--ANCHOR QUERY - Return all programs that have no dependents or are at the top of the chain
SELECT
PackageID,
ProgramName,
CAST(NULL AS VARCHAR(8)) AS ParentPackageID,
CAST(NULL AS VARCHAR(100)) AS ParentPackageName,
CAST(PackageID+ProgramName AS VARCHAR(MAX)) AS [Path],
0 AS [Level]
FROM
dbo.v_Program
WHERE
DependentProgram = ''
UNION ALL
--RECURSIVE QUERY - recursively join to v_Program to get level 1,2,3,etc.
SELECT
child.PackageID,
child.ProgramName,
CAST(parent.PackageID AS VARCHAR(8)) AS ParentPackageID,
CAST(parent.ProgramName AS VARCHAR(100)) AS ParentPackageName,
CAST(parent.[Path]+child.PackageID+child.ProgramName AS VARCHAR(MAX)) AS [Path],
parent.[Level]+1 AS [Level]
FROM
dbo.v_Program AS child
INNER JOIN ProgramHierarchy AS parent
ON parent.PackageID = LEFT(child.DependentProgram,8)
AND parent.ProgramName = SUBSTRING(child.DependentProgram,CHARINDEX(';',child.DependentProgram,10)+1,999)
)
SELECT
REPLICATE(' ',[Level])+PackageID+';'+ProgramName AS [Package;Program Name],
[Level]
FROM
ProgramHierarchy
ORDER BY
[Path]
Number2 (John Nelson)
MyITForum - Forum Posts
MyITForum - Blog
