13
« on: June 19, 2020, 02:32:04 am »
The below worked in case anyone needs it. One odd thing was if I used the alias CLASSTYPE, I got an odd column containing symbols. Now that I have the query, how can I provide less skilled users with a way to run it and export the data? Can I put it into a script?
Geert this is really good stuff - I appreciate your attention to my posts.
Bill
SELECT o.ea_guid AS CLASSGUID, o.Object_Type AS CLASS_TYPE,
re.Name AS Release, cr.StereoType AS ChangeType, cr.Name AS ChangeRequest, o.name AS ChangedItem,
mid(op.notes, instr(op.notes, "date=")+5, 10) AS ChangeDate,
mid(op.notes, instr(op.notes, "comments=")+9, 500) AS ChangeComments,
mid(op.notes, instr(op.notes, "user=")+5, instr(op.notes, ";date") - instr(op.notes, "user=")-5) AS ChangeUser,
package.name AS PackageName, package_p1.name AS Package_level1, package_p2.name AS Package_level2, package_p3.name AS Package_level3
FROM ((((((((t_object o
INNER JOIN t_objectproperties op ON o.Object_ID = op.Object_ID)
INNER JOIN t_object cr ON cr.ea_guid = op.value)
LEFT JOIN t_connector crre ON crre.Start_Object_ID = cr.Object_Id)
LEFT JOIN t_object re ON (re.Object_Id = crre.End_Object_Id AND re.StereoType = 'Release'))
INNER JOIN t_package package ON o.package_id = package.package_id)
LEFT JOIN t_package package_p1 ON package_p1.package_id = package.parent_id)
LEFT JOIN t_package package_p2 ON package_p2.package_id = package_p1.parent_id)
LEFT JOIN t_package package_p3 ON package_p3.package_id = package_p2.parent_id)
WHERE op.Property = 'CR'
UNION
SELECT a.ea_guid AS CLASSGUID, 'Attribute' AS CLASS_TYPE,
re.Name AS Release, cr.StereoType AS ChangeType, cr.Name AS ChangeRequest, o.name +'.'+ a.Name AS ChangedItem,
mid(atv.notes, instr(atv.notes, "date=")+5, 10) AS ChangeDate,
mid(atv.notes, instr(atv.notes, "comments=")+9, 500) AS ChangeComments,
mid(atv.notes, instr(atv.notes, "user=")+5, instr(atv.notes, ";date") - instr(atv.notes, "user=")-5) AS ChangeUser,
package.name AS PackageName, package_p1.name AS Package_level1, package_p2.name AS Package_level2, package_p3.name AS Package_level3
FROM (((((((((t_attribute a
INNER JOIN t_attributetag atv ON a.ID = atv.ElementID)
INNER JOIN t_object cr ON cr.ea_guid = atv.value)
LEFT JOIN t_connector crre ON crre.Start_Object_ID = cr.Object_Id)
LEFT JOIN t_object re ON (re.Object_Id = crre.End_Object_Id AND re.StereoType = 'Release'))
INNER JOIN t_object o ON a.Object_ID = o.Object_ID)
INNER JOIN t_package package ON o.package_id = package.package_id)
LEFT JOIN t_package package_p1 ON package_p1.package_id = package.parent_id)
LEFT JOIN t_package package_p2 ON package_p2.package_id = package_p1.parent_id)
LEFT JOIN t_package package_p3 ON package_p3.package_id = package_p2.parent_id)
WHERE atv.Property = 'CR'
ORDER BY Release, ChangeType, ChangeRequest, ChangedItem
Show Posts
