Sparx Systems Forum

Enterprise Architect => General Board => Topic started by: Eamonn John Casey on January 10, 2018, 11:04:40 pm

Title: SQL to find mult connectors between to elements
Post by: Eamonn John Casey on January 10, 2018, 11:04:40 pm

Hi!
Does anyone want to share a nifty SQL that finds more than one Connector between to elements? Using "Hide Connector" and similar functionality has been a complete disaster for us. There are some pairs of elements in our model that have many connectors btween them. Makes creating New diagrams labourous to hide unnecessary eones.

I know the tables involved but not really a SQL guru!

Thanks,
Eamonn J.

Title: Re: SQL to find mult connectors between to elements
Post by: Geert Bellekens on January 10, 2018, 11:51:39 pm

try this:

Code: [Select]

select os.ea_guid AS CLASSGUID, os.Object_Type AS CLASSTYPE, os.Name as SourceObject, os.ea_guid as SourceGUID,
c.Connector_Type as ConnectorType, ot.Name as TargetName, ot.ea_guid as targetGUID, count(*)
from ((t_connector c
inner join t_object os on os.Object_ID = c.Start_Object_ID)
inner join t_object ot on ot.Object_ID = c.End_Object_ID)
group by os.ea_guid , os.Object_Type , os.Name , os.ea_guid , c.Connector_Type , ot.Name , ot.ea_guid
having count(*) > 1
order by count(*) desc
Geert

Title: Re: SQL to find mult connectors between to elements
Post by: qwerty on January 11, 2018, 12:29:50 am

That will output all elements with more than one connector. I guess the OP is after a list of connectors for two distinct element?

q.

Title: Re: SQL to find mult connectors between to elements
Post by: Geert Bellekens on January 11, 2018, 12:45:11 am

Quote from: qwerty on January 11, 2018, 12:29:50 am

That will output all elements with more than one connector. I guess the OP is after a list of connectors for two distinct element?

q.

No it won't, see the grouping.
It will output all element/connector details for all elements that have 2 or more of the same kind of connector to the same element (which makes them a candidate for duplicate connectors, although there might be valid exceptions)

Geert

Title: Re: SQL to find mult connectors between to elements
Post by: Eamonn John Casey on January 11, 2018, 01:32:28 am

Geert
Thanks! I now got a start point to tweek With.

The scenario I am looking for is (using ArchiMate speak):
A is Assocaited With B
B is Serving A

So I am looking for Connectors in both directions and of same or different types. I'll post the result when I get to it.  :)

/ Eamonn J. //

Title: Re: SQL to find mult connectors between to elements
Post by: Paolo F Cantoni on January 11, 2018, 10:40:11 am

Quote from: Geert Bellekens on January 11, 2018, 12:45:11 am

Quote from: qwerty on January 11, 2018, 12:29:50 am

That will output all elements with more than one connector. I guess the OP is after a list of connectors for two distinct element?

q.

No it won't, see the grouping.
It will output all element/connector details for all elements that have 2 or more of the same kind of connector to the same element (which makes them a candidate for duplicate connectors, although there might be valid exceptions)

Geert

Candidacy is relatively easy.  Actually determining whether two connectors are semantically the same (and perhaps consolidating properties that are not semantically significant) is non-trivial.  We have a version which we are testing as part of our element consolidation process.

Paolo