Sparx Systems Forum

Enterprise Architect => Suggestions and Requests => Topic started by: wfsc on April 22, 2015, 12:50:50 am

Title: SQL query left join with nested select
Post by: wfsc on April 22, 2015, 12:50:50 am

Hi,
i'm trying to retrieve requirements from a locally stored repository. The idea was, to retrieve the requirements hierarchy by selecting the parent object id (if any) via an existing relationship for each requirement. I tried to use the following SQL query:

select o.Object_ID, o.Name, o.Object_Type, t.Start_Object_ID as Parent_Object_ID
from t_object o
left join (select Start_Object_ID, End_Object_ID from t_connector where Stereotype = 'deriveReqt') t on o.Object_ID = t.End_Object_ID
where
      o.Object_Type = 'Requirement'

Unfortunately, it ends with an SQL error message. I believe, that there could be some restrictions about the nested left join / select construct. Is there any suggestion about such a selection?

Thank you in advance.

Wolfgang

Title: Re: SQL query left join with nested select
Post by: qwerty on April 22, 2015, 05:10:10 am

You're probably targeting an EAP (aka M$ Access) which has quite limited SQL capabilities (and even those are somehow screwed up). Try with a "real" RDBMS.

q.

Title: Re: SQL query left join with nested select
Post by: Geert Bellekens on April 22, 2015, 06:25:50 pm

Wolfgang,

You better rewrite your SQL.
On something like SQL Server you can write it as

Code: [Select]

select o.Object_ID, o.Name, o.Object_Type, c.Start_Object_ID as Parent_Object_ID
from t_object o
left join t_connector c on c.End_Object_ID = o.Object_ID and c.Stereotype = 'deriveReqt'
where o.Object_Type = 'Requirement' but on an eap you have to use

Code: [Select]

select o.Object_ID, o.Name, o.Object_Type, c.Start_Object_ID as Parent_Object_ID
from (t_object o
left join t_connector c on c.End_Object_ID = o.Object_ID )
where o.Object_Type = 'Requirement'
and (c.Stereotype = 'deriveReqt' or c.Stereotype is null)
Geert