SQL search relations between nested objects with conveyed item

[Thorn]

Hi,

I have a Component diagram. There are Components with Ports connected by Information Flow. Information Flow has Items conveyed.
I need to create list of all relationships within the diagram (to generate integration catalog out of the diagram). As all the Components are placed in one Package as a reusable assets, I need to filter the search on the level of diagram, or maybe specific Tagged Value

I have tried following search (TOP 50 is used only for debug because many objects are within the database):

Code: [Select]

SELECT TOP 50
  PRODUCER.Stereotype AS Producer_Type,
      PRODUCER.Name AS Producer_Name,
  CONSUMER.Stereotype AS Consumer_Type,
      CONSUMER.Name AS Consumer_Name,
  CONNECTOR.Name AS Purpose,
      CONNECTOR.Stereotype AS Connector_Type,
      CONVEYED.Stereotype AS Conveyed_Type,
      CONVEYED.Name AS Data_Entity,
      PACKAGE.Name AS Package,
  DIAGRAM.Name AS Diagram,
  TAGS.Property AS Tag_Name,
  TAGS.VALUE AS Tag_Value
FROM t_xref XCONVEYED, t_object CONVEYED,
      t_object PRODUCER, t_object CONSUMER,
      t_connector CONNECTOR, t_object PACKAGE,
  t_diagram DIAGRAM, t_objectproperties TAGS
WHERE XCONVEYED.Behavior='conveyed'
      AND XCONVEYED.Description LIKE '%' + CONVEYED.ea_guid + '%'
      AND CONNECTOR.Start_Object_ID=PRODUCER.Object_ID
      AND CONNECTOR.End_Object_ID=CONSUMER.Object_ID
      AND CONNECTOR.ea_guid = XCONVEYED.Client
  AND DIAGRAM.Name = 'Relations search'

but this will select only direct relations between ports without superior Component. And even the filtering doesn't work

any idea?

[qwerty]

Not going to try to understand the SQL. For me, when SQL simply get's too weird, writing a script works best where I use a rather simple but performant SQL and using the script to re-work the result with the tricky parts.

q.

[Geert Bellekens]

I wrote a query for conveyed items on connectors between ports a while ago.
I don't remember the details right now, but it might be helpful.
I think for complex queries you should really use the join syntax to keep thing a bit sane.

Code: [Select]

select
ost.[Name] as Source,
c.[Name] as ConnectorName, ose.[Name] as Target,io.Name as Conveyed, #DB=JET#IIF(io.Stereotype is null, null, '«' + io.Stereotype + '»')#DB=JET#
#DB=Other#CASE when io.Stereotype is not null THEN '«' + io.Stereotype + '»' ELSE null END #DB=Other#
as ConveyedStereo
from (((((((((t_diagram d
inner join [t_diagramlinks] dl on dl.[DiagramID] = d.[Diagram_ID])
inner join [t_connector] c on dl.[ConnectorID] = c.[Connector_ID])
inner join [t_object] ost on ( ost.[Object_ID] = c.[Start_Object_ID]
                                          and ost.[Object_Type] = 'Port'))
inner join [t_object] ose on ( ose.[Object_ID] = c.[End_Object_ID]
                                            and ose.[Object_Type] = 'Port'))
inner join [t_diagramObjects] dob on dob.[Diagram_ID] = d.[Diagram_ID])
left join t_xref x on (x.[Client] = c.ea_guid
                      and x.[Behavior] = 'abstraction'))
left join t_connector ci on left(x.description, 255) like '#WC#' + ci.[ea_guid] + '#WC#')
left join t_xref xi on (xi.[Client] = ci.[ea_guid]
                              and xi.[Behavior] = 'conveyed'))
left join t_object io on left(xi.[Description],255) like  '#WC#' + io.[ea_guid] + '#WC#')                                                                                       
where d.[Diagram_ID] = #DIAGRAMID#
and dob.[Object_ID] = c.[Start_Object_ID]
and c.[Connector_Type] = 'Connector'
and dl.[Hidden] = 0
order by connectorName asc
Geert