Sparx Systems Forum

Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: Daiim on August 23, 2021, 07:49:59 am

Title: SQL Query for two (or more) neighbours
Post by: Daiim on August 23, 2021, 07:49:59 am

Hi,

I try to query two neighbors of a node but EA complains about invalid JOIN statement. Can anyone help?

Code: [Select]

SELECT objA.Name AS AName, objB.Name as BName, objC.Name CName
FROM (((t_connector relOne, t_connector relTwo
LEFT JOIN t_object objA ON (relOne.Start_Object_ID = objA.Object_ID AND relTwo.Start_Object_ID = objA.Object_ID))
LEFT JOIN t_object objB ON relOne.End_Object_ID = objB)
LEFT JOIN t_object objC ON relTwo.End_Object_ID = objC)
WHERE objA.Stereotype = 'ValueA' AND objB.Stereotype = 'ValueB' AND objC.Stereotype = 'ValueC'

Any ideas? Thank you for your help.

Title: Re: SQL Query for two (or more) neighbours
Post by: Eve on August 23, 2021, 08:19:20 am

You haven't specified which database you're running on, which means I'm only guessing.

I think it's a bad idea (in at least one supported db) to mix a comma join with explicit joins.

Title: Re: SQL Query for two (or more) neighbours
Post by: Daiim on August 23, 2021, 03:49:15 pm

Do you refer to the first JOIN in context of t_connector relOne, t_connector relTwo?

Quote from: Daiim on August 23, 2021, 07:49:59 am

Code: [Select]

SELECT objA.Name AS AName, objB.Name as BName, objC.Name CName
FROM (((t_connector relOne, t_connector relTwo
LEFT JOIN t_object objA ON (relOne.Start_Object_ID = objA.Object_ID AND relTwo.Start_Object_ID = objA.Object_ID))
LEFT JOIN t_object objB ON relOne.End_Object_ID = objB)
LEFT JOIN t_object objC ON relTwo.End_Object_ID = objC)
WHERE objA.Stereotype = 'ValueA' AND objB.Stereotype = 'ValueB' AND objC.Stereotype = 'ValueC'


So the query should be something like:

Code: [Select]

SELECT objA.Name AS AName, objB.Name as BName, objC.Name CName
FROM ((((t_connector relOne, t_connector relTwo
LEFT JOIN t_object objA ON relOne.Start_Object_ID = objA.Object_ID)
LEFT JOIN t_object objD ON relTwo.Start_Object_ID = objD.Object_ID)
LEFT JOIN t_object objB ON relOne.End_Object_ID = objB)
LEFT JOIN t_object objC ON relTwo.End_Object_ID = objC)
WHERE objA.ea_guid = objD.ea_guid AND objA.Stereotype = 'ValueA' AND objB.Stereotype = 'ValueB' AND objC.Stereotype = 'ValueC'

I've tried this one too, but also got complains about the JOIN syntax.

This query works, but object B and C are always the same, so the WHERE clause on stereotype does not work.

Code: [Select]

SELECT objA.Name AS AName, objB.Name as BName, objC.Name CName
FROM ((((t_connector rel
LEFT JOIN t_object objA ON rel.Start_Object_ID = objA.Object_ID)
LEFT JOIN t_object objD ON rel.Start_Object_ID = objD.Object_ID)
LEFT JOIN t_object objB ON rel.End_Object_ID = objB)
LEFT JOIN t_object objC ON rel.End_Object_ID = objC)
WHERE objA.ea_guid = objD.ea_guid AND objA.Stereotype = 'ValueA' AND objB.Stereotype = 'ValueB' AND objC.Stereotype = 'ValueC'


Database is an *.eap file repository.

Title: Re: SQL Query for two (or more) neighbours
Post by: Geert Bellekens on August 23, 2021, 04:33:59 pm

You have to join t_connect relTwo the same way you are joining the other tables.
something similar to this (but you need to add a bunch parentheses for .eap)

Code: [Select]

from t_object g
left join t_connector cpg on cpg.End_Object_ID = g.Object_ID
and cpg.Stereotype = 'Archimate_Aggregation'
left join t_object pg on pg.Object_ID = cpg.Start_Object_ID
and pg.Stereotype = 'EAM_UserGroup'
left join t_connector cpg2 on cpg2.End_Object_ID = pg.Object_ID
and cpg2.stereotype = 'Archimate_Aggregation'
left join t_object pg2 on pg2.Object_ID = cpg2.Start_Object_ID
and pg2.Stereotype = 'EAM_UserGroup'
left join t_connector cpg3 on cpg3.End_Object_ID = pg2.Object_ID
and cpg3.stereotype = 'Archimate_Aggregation'
left join t_object pg3 on pg3.Object_ID = cpg3.Start_Object_ID
and pg3.Stereotype = 'EAM_UserGroup'
Geert