Get related Elements with a single query (Oracle)

When working with Sparx EA I often want to generate a report to discuss a set of Elements in the context of their relationship to other elements.

I’ve made the following query that returns:

1. Connected Elements: to the current element (current <- other)
2. Connected Elements: from the current element (current -> other)
3. Child Elements: directly under the current element in the project browser, not grandchildren etc.
4. Child Elements: identified in the current element’s child diagram(s). E.g. the current element is a process element with a process diagram underneath it, all activities in that process diagram would be listed.
5. Parent Element: direct parent of the current element
6. Parent Element: indirect, the current element appears in a diagram that is a child diagram of the parent element.
7. In the notes of the current element as hyperlinks.

SQL Statement

Uses the ORACLE function LISTAGG to create a list of hyperlinks

SELECT ElementType, LISTAGG(Alias_Formated,', ') WITHIN GROUP (ORDER BY Alias) ["List.Formatted"]
FROM
(
	SELECT UNIQUE t_object.Alias, t_object.ea_guid, t_object.Stereotype, t_object.Object_ID, 
	SUBSTR(t_object.Alias,0,INSTR(t_object.Alias,'-')-1) AS ElementType,
	CONCAT('<a href="$element://',
	  CONCAT(t_object.ea_guid,CONCAT('"><font color="#0000ff"><u>',
	  CONCAT(t_object.Alias,'</u></font></a>')))) AS Alias_Formated
	FROM t_object
	WHERE t_object.Object_ID IN 
	(
		SELECT t_connector.Start_Object_ID AS Object_ID FROM t_connector WHERE t_connector.End_Object_ID = #OBJECTID# 
		UNION ALL
		SELECT t_connector.End_Object_ID AS Object_ID FROM t_connector WHERE t_connector.Start_Object_ID = #OBJECTID#
		UNION ALL
		SELECT t_object.Object_ID FROM t_OBJECT WHERE t_object.ParentID = #OBJECTID#
		UNION ALL
		SELECT t_diagramobjects.Object_ID FROM t_diagramobjects 
		INNER JOIN t_diagram ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID WHERE t_diagram.ParentID = #OBJECTID#
		UNION ALL
		SELECT t_object.Object_ID FROM t_object qParent
		INNER JOIN t_object
		ON qParent.Object_ID = t_object.ParentID
		WHERE t_object.Object_ID = #OBJECTID#
		UNION ALL
		SELECT t_object.Object_ID FROM t_object 
		INNER JOIN t_diagram
		ON t_diagram.ParentID = t_object.Object_ID
		INNER JOIN t_diagramobjects
		ON t_diagramobjects.Diagram_ID = t_diagram.Diagram_ID
		WHERE t_diagramobjects.Object_ID = #OBJECTID#
		UNION ALL
		SELECT t_object.Object_ID FROM t_object 
		WHERE t_object.ea_guid IN
		(
			SELECT to_char(REGEXP_SUBSTR(partial_str, '\{(.*?)\}', 1, 1)) AS found_GUID
			FROM
			(
				SELECT REGEXP_SUBSTR(str, '[^\$]+', 1, LEVEL) AS partial_str
				FROM
					(SELECT t_object.Note AS str FROM t_object WHERE t_object.Object_ID = #OBJECTID#)
				CONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE(str, '[^\$]+')) + 1
			)
			WHERE LENGTH(partial_str) > 0
		)		
	)
	ORDER BY ElementType
)
GROUP BY ElementType

Result

The result in my report is a table of the “related” elements grouped by element type. The hyperlinks use Sparx EA bookmarks in the document to link to the details about that element. Currently debating if this should be updated to link to our WebEA server.