Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: jvdens on March 15, 2022, 10:40:00 am
-
Hi, I'm struggling with a SQL query for reporting on a software catalogue based on the following model elements in EA:
- we use components to represent software tools (stereotype "tool"),
- each tool can have one vendor associated to it (component with stereotype "company"),
- each tool can have many capabilities associated to it (activities with stereotype "capability").
I try to create a SQL query that has this desired result:
name | notes | vendor | capabilities
----------------------------------------------------------
tool 1 | description of tool 1 | vendor x | cap1, cap2, cap3
tool 2 | description of tool 2 | vendor y | cap2, cap4
The hard part is: the comma separated list of capabilities.
I'm halfway: I created a query that results in the correct collums, but has too many row (each for one of related capabiliies):
name | notes | vendor | capabilities
-----------------------------------------------------------
tool 1 | description of tool 1 | vendor x | cap1
tool 1 | description of tool 1 | vendor x | cap2
tool 1 | description of tool 1 | vendor x | cap3
tool 2 | description of tool 2 | vendor y | cap2
tool 2 | description of tool 2 | vendor y | cap4
We use a SQL Server DB, (and EA 15.2).
Here's the "halfway" query - I know the answer probably relies on SQL functions like STRING_AGG, but I can't find how to apply..
----------------------------------------------------------------------------------------------------
SELECT t_object.ea_guid AS CLASSGUID, t_object.Name , t_object.Note , VendorName , Capability
from t_object
-- look for associated vendor
left join (select vendorconnector.Start_Object_ID, thevendor.Name as VendorName
from t_connector vendorconnector
inner join t_object thevendor on ( thevendor.Object_ID = vendorconnector.End_Object_ID )
where (thevendor.Stereotype = 'company'))
AS vendorjoin
ON vendorjoin.Start_Object_ID = t_object.Object_ID -- left join with answer
-- look for associated capabilities
left join (select capabilityconnector.Start_Object_ID, theCapability.Name as Capability
from t_connector capabilityconnector
inner join t_object theCapability on ( theCapability.Object_ID = capabilityconnector.End_Object_ID )
where (theCapability.Stereotype = 'capability'))
AS capjoin
ON capjoin.Start_Object_ID = t_object.Object_ID -- left join with answer2
-- basic conditions for the resulting t_objects
where ( ( t_object.Stereotype = 'tool' ) and (t_object.Object_type = 'Component') )
ORDER BY t_object.Name
------------------------------------------------------------------------------------------------------
Any help would be greatly appreciated!!
-
I use a combination of REPLACE, STUFF and FOR XML PATH do do the same. This was the only technique you could use before STRING_AGG was available.
I do believe you can get the same result (but easier) with STRING_AGG, but I haven't gotten around to try that yet.
select rq.Name as Requirement,
Case
when rq.stereotype = 'Compatibilit' then 'Integriteit'
when rq.stereotype = 'Reliability' then 'Beschikbaarheid'
when rq.stereotype = 'Functional' then 'Privacy'
when rq.stereotype = 'Security' then 'Vertrouwelijkheid'
else rq.Stereotype
end as RQType
, rq.Note as [Description-Formatted]
,'todo' as Systems
,Replace((
select STUFF((
select distinct ',' + s.Name
from t_connector cs
inner join t_object s on s.Object_ID = cs.End_Object_ID
and s.Stereotype = 'EAM_ApplicationComponent'
where cs.Start_Object_ID = rq.Object_ID
and cs.Stereotype = 'trace'
FOR XML PATH ('')
),1,1,'')
), ',', char(13) + char(10) ) as [Systems-Formatted]
from t_object o
inner join t_connector c on c.End_Object_ID = o.Object_ID
and c.Stereotype = 'trace'
inner join t_object rq on rq.Object_ID = c.Start_Object_ID
and rq.Object_Type = 'Requirement'
where o.Object_ID = #OBJECTID#
Geert
-
Hi Geert,
thank you VERY much for this fine piece of SQL magic!!!
I applied it to my "half way" query.. and it works! :D :D :D and I probably will be using this pattern a lot in many other use cases.
grtz, Joris
here's the result:
SELECT t_object.ea_guid AS CLASSGUID, t_object.Name, t_object.Note,
Replace((
select STUFF((
select distinct ',' + s.Name
from t_connector cs
inner join t_object s on s.Object_ID = cs.End_Object_ID and s.Stereotype = 'capability'
where cs.Start_Object_ID = t_object.Object_ID
FOR XML PATH ('')
),1,1,'')
), ',', char(13) + char(10) ) as [Capabilities],
VendorName
from t_object
-- look for associated vendor
left join (select vendorconnector.Start_Object_ID, thevendor.Name as VendorName
from t_connector vendorconnector
inner join t_object thevendor on ( thevendor.Object_ID = vendorconnector.End_Object_ID )
where (thevendor.Stereotype = 'company'))
AS vendorjoin
ON vendorjoin.Start_Object_ID = t_object.Object_ID -- left join with answer
-- basic conditions for the resulting t_objects
where ( ( t_object.Stereotype = 'tool' ) and (t_object.Object_type = 'Component') )
ORDER BY t_object.name
-
No time to read in detail, but is this not a job for STRING_AGG? Your could STRING_AGG(this, ' | ') || STRING_AGG(that, ', ')
-
No time to read in detail, but is this not a job for STRING_AGG? Your could STRING_AGG(this, ' | ') || STRING_AGG(that, ', ')
Yes, we mentioned that. My code is from before STRING_AGG existed, so this was the workaround back then.
Would be interesting to see how the query looks with STRING_AGG
Geert