Excel, Oracle and Sparx EA Part 3 of 5: Tagged Values as columns

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:

1. Pivot, to create columns for taggedvalues
2. Case to support both taggedvalue values and taggedvalue notes.
3. 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)