Multiple Joins in SQL Query Builder

[Shaun Flynn]

I am loving the new fragments in the doc gen functionality and the ability to add SQL as document Options. It makes the production of Documents much more targeted.

However I was wondering if there is a bug around adding multiple JOINS to the SQL Query Builder strings.

I can get the SQL to work with a single JOIN, however when I add to working Joins together into the sql query it constantly reports an error when I try to run it.

Thanks

S

[Helmut Ortmann]

Hello,

due to my experiences multiple joins work correctly.

You can put the Query in the EA Search Window to test it without bothering about fragments.

Maybe put your query string into this forum. Also tell if you use something else than an *.eap file (a DBMS like SQL Server).

Helmut

[Shaun Flynn]

Ok so first I must point out that my SQL is at best ropey, however here is my scenario. I have a Service (A component) that has a realisation to an interface which obviously has a name and some notes.

I want to be able to write some SQL that walks the realisation link to get the end object and list the name and notes.

So looking at the Schmea in the Project Management with UML and EA book on page 244 (by the way is there a new version of this scema anywhere?) I am assuming I need a join between the t_connector, t_object and t_objectproperties tables?

My first attempt was thus:-

SELECT t_Connector.ea_guid AS CLASSGUID, t_Connector.Connector_Type AS CLASSTYPE, t_Connector.*, endObject.*, endObjectProps.*
FROM t_Connector
INNER JOIN t_Object as endObject ON t_Connector.End_Object_ID=endObject.Object_ID
where t_Connector.Connector_Type = 'Realisation' and endObject.Object_Type = 'Interface' and t_Connector.Start_Object_ID = 22822
INNER JOIN t_ObjectProperties as endObjectProps ON t_Object.Object_ID=endObjectProps.Object_ID
where t_ObjectProperties.Object_ID = endObject.Object_ID

Upon speaking with someone here they suggested that EA may not like the two where statements so I should join them on the end thus:-

SELECT t_Connector.ea_guid AS CLASSGUID, t_Connector.Connector_Type AS CLASSTYPE, t_Connector.*, endObject.*, endObjectProps.*
FROM t_Connector
INNER JOIN t_Object as endObject ON t_Connector.End_Object_ID=endObject.Object_ID
INNER JOIN t_ObjectProperties as endObjectProps ON t_Object.Object_ID=endObjectProps.Object_ID
where t_Connector.Connector_Type = 'Realisation' and endObject.Object_Type = 'Interface' and t_Connector.Start_Object_ID = 22822 and t_ObjectProperties.Object_ID = endObject.Object_ID

As I said my SQL is not great so any help would be much appreciated.

Thanks
S

[Shaun Flynn]

Ahha! I seem to have solved it I think.

I need to use bracket to seperate out the join statements. Plus I realised I didn't need the second join to the ObjectProperties table as the notes come as part of the Object.

So I have now used:-

SELECT t_Connector.ea_guid AS CLASSGUID, t_Connector.Connector_Type AS CLASSTYPE, t_Connector.*, startObject.*, endObject.*
FROM ((t_Connector
INNER JOIN t_Object as startObject ON t_Connector.Start_Object_ID=startObject.Object_ID)
INNER JOIN t_Object as endObject ON t_Connector.End_Object_ID=endObject.Object_ID)
where t_Connector.Connector_Type = 'Realisation' and endObject.Object_Type = 'Interface' and t_Connector.Start_Object_ID = 22822

And can get the notes for both start and end objects!
O the power of trial and error!  
Thanks
S

[Helmut Ortmann]

Hello,

1. Notes
The notes is in t_object.note. There is no need to read objectproperties.

2. Inner Join for *.eap
SELECT *
FROM t_connector con, t_Object as o, t_ObjectProperties prop
where con.end_object_id = o.object_id AND
          o.Object_ID=prop.Object_ID
I think in *.eap files, in effect an MSACCESS database, are some problems with inner joins. Just transform it into ordinary where clause as in the above example showed.

Helmut

[Shaun Flynn]

Is there a newer schema that I can have access to please?

My schema does not have the Note on the t_object table.
My Version is marked 2010.

Thanks
S

[qwerty]

The Note is present, but does not show up per default (probably because of multi-line issues). If you want to see it:

Code: [Select]

select t_object.Note as [Notex] from t_object
q.

[Shaun Flynn]

Thanks for the reply, my SQL query does return the note, that is not the problem.

I am trying to get hold of a newer schema diagram for the EA model as the one in the "Project Development with UML and EA" book (page 244) does not show the note as an attribute of the t_Object table.

This schema is dated 2010 and so I am sure that there is a newer more upto date version.

I was wondering if anybody has a link to one?

Thanks
S

[qwerty]

Maybe you should contact the author. Note is still and has ever been a column in t_object.

q.

[Eve]

The Note is present, but does not show up per default (probably because of multi-line issues). If you want to see it:

Code: [Select]

select t_object.Note as [Notex] from t_object
q.

No issue. The default behavior is to detect a column of that name and insert the special notes row. You then have an option of how much to show. (Fourth icon on the toolbar)