Search like #Branch# but from a named package?

[johnd-im]

I know there's a lot of discussion on recursive SQL queries and use of #Branch#  macro, etc. What I cannot find is how to search through a package hierarchy starting from a named package (hardwired in query or specified using <Search Term>, not using #Branch# or #Package# macros that depend on a package being selected first in the Project Browser).

Kind of like this but need it to search down through child packages of the specified package:

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name as Object from t_object, t_package as p where p.name ='<Search Term>' and t_object.package_id = p.package_id

[qwerty]

I guess you can't. #Branch# is always the selected package. There's no #subpackagesOf:<guid># You'd need to script that.

q.

[Geert Bellekens]

In this case I would probably figure out just how deep you need to go and then join t_package that many times.

I've done such a thing here at my client and I believe we never go deeper then 10 packages.

I know it's a hack, but sometimes you just need to be pragmatical.

Geert

[johnd-im]

Thanks qwerty and Geert!

Qwerty: Agree that a #subpackagesOf:<guid># or even a #subpackagesOf:<name># style macro would be most useful.  Until then...

Geert: Pragmatical totally ROCKS!  Your classic May 2013 posting "Re: SQL query :: Returns Node Path Elements" (http://www.sparxsystems.com/cgi-bin/yabb/YaBB.cgi?num=1368695770/0) and of course your blog (http://bellekens.com/2011/01/14/harvesting-the-power-of-eas-sql-searches/) were enough to inspire a hacked pure SQL solution. Crude but effective.

Find all elements stereotyped 'System' on the specified branch of a package hierarchy:

Code: [Select]

SELECT t_object.ea_guid AS CLASSGUID, t_object.Object_Type AS CLASSTYPE, t_object.Name as Object,
t_object.Object_Type as Type, t_object.Stereotype, t_object.Scope, t_object.Status, t_object.Phase,
t_object.CreatedDate as Created, t_object.ModifiedDate as Modified
FROM t_object WHERE Stereotype='System' and Package_ID IN (
SELECT p.Package_ID
FROM (((((((((t_object as o  
left join t_package p on o.ea_guid = p.ea_guid )
left join t_package p2 on p.Parent_ID = p2.Package_ID )
left join t_package p3 on p2.Parent_ID = p3.Package_ID )
left join t_package p4 on p3.Parent_ID = p4.Package_ID )
left join t_package p5 on p4.Parent_ID = p5.Package_ID )
left join t_package p6 on p5.Parent_ID = p6.Package_ID )
left join t_package p7 on p6.Parent_ID = p7.Package_ID )
left join t_package p8 on p7.Parent_ID = p8.Package_ID )
left join t_package p9 on p8.Parent_ID = p9.Package_ID )
WHERE (p.name = '<Search Term>' or p2.name = '<Search Term>' or p3.name = '<Search Term>'  or p4.name = '<Search Term>' or p5.name = '<Search Term>' or p6.name = '<Search Term>' or p7.name = '<Search Term>' or p8.name = '<Search Term>' or p9.name = '<Search Term>')
)
I use this query and similar variations to scope the source and target lists in my traceability (relationship) matrices (e.g., system-function, system-system and system-server).

Thanks again!
-jd

[qwerty]

EA teaches you pragmatism.

q.