12
« on: August 02, 2024, 12:00:12 am »
Or find here a MSSQL query I use as a MSSQL view on my server which generates for each t_object a "ID Path" an "Name Path" and a GUID Path" which can be used to filter path related elements.
WITH PathBuilder(Parent_ID, Package_ID, Package_Name, GUIDPath, NamePath, IDPath) AS (SELECT Parent_ID, Package_ID, Name, CAST(ea_guid AS VARCHAR(1000)) AS GUIDPath, CAST(Name AS VARCHAR(1000)) AS NamePath,
CAST(Package_ID AS VARCHAR(1000)) AS IDPath
FROM dbo.t_package AS pkg
WHERE (Parent_ID = 0)
UNION ALL
SELECT pkg.Parent_ID, pkg.Package_ID, pkg.Name, CAST(pb.GUIDPath + '.' + pkg.ea_guid AS VARCHAR(1000)) AS GUIDPath,
CAST(pb.NamePath + '.' + pkg.Name AS VARCHAR(1000)) AS NamePath,
CAST(pb.IDPath + '.' + CAST(pkg.Package_ID AS VARCHAR(1000)) AS VARCHAR(1000)) AS IDPath
FROM dbo.t_package AS pkg INNER JOIN
PathBuilder AS pb ON pkg.Parent_ID = pb.Package_ID)
SELECT DISTINCT Parent_ID, Package_ID, Package_Name, GUIDPath, NamePath, IDPath
FROM PathBuilder AS pb