In my previous post I described how I was able to use different Oracle functions to maintain the hierarchy for the Packages and Elements so they would appear in the same order in Excel as they appear in my Project Browser in Sparx EA. The next step was to figure out how to include the tagged values for each of Packages and Elements in the results.
The following generic SQL and proprietary Oracle SQL functions were used:
- Pivot, to create columns for taggedvalues
- Case to support both taggedvalue values and taggedvalue notes.
- Join, to combine the Hierarchy from my previous post with the tagged values.
1.0: Pivot, to create columns for taggedvalues
In Sparx EA the tagged values for packages and elements are stored in the table t_objectproperties with each row containing a different tagged value.
I was able to join the t_object table to t_objectproperties and then use the pivot function to display two additional columns with the values for: Security Integrity, and Data Type.
SELECT t3.Value AS taggedValue, * FROM t_object t1 LEFT JOIN t_objectproperties t3 ON t3.Object_ID = t1.Object_ID WHERE t_object.ea_guid = '{4200F244-23AA-4eed-B70A-9E88CEBC661B}' PIVOT ( max(taggedValue) FOR property IN ( 'Security Integrity' AS "Security Integrity", 'Data Type' AS "Data Type" ) )
Value is a reserved word in Oracle, and I wasn’t able to used t3.value in the PIVOT function, and can’t seem to use [value] in Excel, so I created an alias for t3.value called taggedValue.
2.0: Case, to support both taggedvalue values and taggedvalue notes.
SELECT t3.Property, ( CASE WHEN t3.Value = '<memo>' THEN to_char(t3.Notes) ELSE t3.Value END ) AS taggedValue FROM t_object t1 LEFT JOIN t_objectproperties t3 ON t3.Object_ID = t1.Object_ID WHERE t_object.ea_guid = '{4200F244-23AA-4eed-B70A-9E88CEBC661B}' PIVOT ( max(taggedValue) FOR property IN ( 'Security Integrity' AS "Security Integrity", 'Data Type' AS "Data Type" ) )
Unfortunately this solution doesn’t support Notes with text longer than 4000 chars. The only other option I can think of would be to use subqueries for taggedvalues that use the notes with longer text. Suggestions?
3.0: Join, to combine the Hierarchy from my previous post with the tagged values
Taking the SQL from my previous post to retrieve packages and elements while maintaining hierarchy, and joining it to the PIVOT SQL from this post, I was able to get what I wanted. I’m aware that this SQL isn’t as optimized as it could be, but I’ve found it works well enough considering that these queries will only be run occasionally, again, I’m open to suggestions…
SELECT SYS_CONNECT_BY_PATH(TO_CHAR(ROWNUM,'000000'),'//') AS HIERARCHY_PATH, t1.ModifiedDate, t1.ea_guid, t1.Name AS EAFullName, t1.stereotype, t1.Note AS Notes, t1.Status, t1.Version, t3.* FROM t_object t1 JOIN t_object t2 ON to_char(t1.Package_ID) = t2.PDATA1 LEFT JOIN ( SELECT * FROM ( SELECT t_objectproperties.Object_ID, t_objectproperties.Property, t_objectproperties.Value AS taggedValue FROM t_objectproperties ) t4 PIVOT ( max(taggedValue) FOR property IN ( 'Security Integrity' AS "Security Integrity", 'Data Type' AS "Data Type" ) ) p1 ) t3 ON t1.Object_ID = t3.Object_ID START WITH t1.Object_ID = ( SELECT t_object.OBJECT_ID FROM t_object WHERE t_object.PDATA1 = to_char(( SELECT t_package.Package_ID FROM t_package WHERE t_package.ea_guid = '{FFEEA7B5-9468-49c2-A69C-B294266C2C0B}' AND ROWNUM=1 )) ) CONNECT BY PRIOR t1.Object_ID = ( CASE WHEN t1.ParentID = 0 THEN t2.Object_ID ELSE t1.ParentID END ) ORDER SIBLINGS BY CONCAT(to_char(NVL(t1.tpos,0),'000'),t1.name)