Another SQL query..

[TheBlueSki]

Searching through a tree of elements and diagrams, to pull out a list of actions with SQL. Working ok, but I can't figure out how to pull the names of I guess linked activies that are display in a diagram as :Something, rather than a name where in properties in a diagram they have been named.

[TheBlueSki]

The elements I would like to find the names for, can be seen in the Properties window.  I need to pull out from CallBehaviorAction  / Bevavior.

[BobM]

:something as in classifiers?

Code: [Select]

SELECT
CLASSGUID = o.ea_guid
,CLASSTYPE = o.object_type
,[RowNumber] = ROW_NUMBER() OVER (ORDER BY
ISNULL(p9.Name + ' / ', '')
+ ISNULL(p8.Name + ' / ', '')
+ ISNULL(p7.Name + ' / ', '')
+ ISNULL(p6.Name + ' / ', '')
+ ISNULL(p5.Name + ' / ', '')
+ ISNULL(p4.Name + ' / ', '')
+ ISNULL(p3.Name + ' / ', '')
+ ISNULL(p2.Name + ' / ', '')
+ ISNULL(p1.Name, ''))
,[Classifier] = c.Name
,[Classifier Type] = c.Stereotype
,[Classifier In Package] = ISNULL(p1.name, '')
,[Path To classifier] = ISNULL(p9.Name + ' / ', '')
+ ISNULL(p8.Name + ' / ', '')
+ ISNULL(p7.Name + ' / ', '')
+ ISNULL(p6.Name + ' / ', '')
+ ISNULL(p5.Name + ' / ', '')
+ ISNULL(p4.Name + ' / ', '')
+ ISNULL(p3.Name + ' / ', '')
+ ISNULL(p2.Name + ' / ', '')
+ ISNULL(p1.Name, '')
,[Object_Type] = o.Object_Type
,[Stereotype] = o.stereotype
,[Author] = o.author
,[CreatedDate] = o.createdDate
,[ModifiedDate] = o.modifiedDate
,[Diagram Name] = dia.name
,[Diagram in Package] = ISNULL(p10.name, '')
,[Path To Diagram] = ISNULL(p19.Name + ' / ', '')
+ ISNULL(p18.Name + ' / ', '')
+ ISNULL(p17.Name + ' / ', '')
+ ISNULL(p16.Name + ' / ', '')
+ ISNULL(p15.Name + ' / ', '')
+ ISNULL(p14.Name + ' / ', '')
+ ISNULL(p13.Name + ' / ', '')
+ ISNULL(p12.Name + ' / ', '')
+ ISNULL(p11.Name + ' / ', '')
+ ISNULL(p10.Name, '')
FROM T_Object o
LEFT JOIN t_diagramobjects dobj
ON o.Object_ID = dobj.object_ID
LEFT JOIN T_Diagram dia
ON dobj.Diagram_ID = dia.Diagram_ID
LEFT JOIN t_object c
ON o.Classifier = c.Object_ID
    LEFT JOIN t_package p1
        ON (c.package_ID = p1.Package_ID)
    LEFT JOIN t_package p2
        ON p1.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
LEFT JOIN t_package p10
        ON (dia.package_ID = p10.Package_ID)
    LEFT JOIN t_package p11
        ON p10.Parent_ID = p11.Package_ID
    LEFT JOIN t_package p12
        ON p11.Parent_ID = p12.Package_ID
    LEFT JOIN t_package p13
        ON p12.Parent_ID = p13.Package_ID
    LEFT JOIN t_package p14
        ON p13.Parent_ID = p14.Package_ID
    LEFT JOIN t_package p15
        ON p14.Parent_ID = p14.Package_ID
    LEFT JOIN t_package p16
        ON p15.Parent_ID = p16.Package_ID
    LEFT JOIN t_package p17
        ON p16.Parent_ID = p17.Package_ID
    LEFT JOIN t_package p18
        ON p17.Parent_ID = p18.Package_ID
    LEFT JOIN t_package p19
        ON p18.Parent_ID = p19.Package_ID
WHERE 1=1
AND o.Classifier <> 0