SQL Join problems

[sean.mcctech]

Hi all new user here,

All I want is a simple table that lists a trace from a requirement to associated requirements. I would eventually like to add in realized use cases and test cases but have to get this down first. Here is what I have so far but keep running into syntax errors.

SELECT
       t_object.NAME        AS [From_Name],
   t_object.note        AS [From_Note],
          --Isnull(op2.value, '') AS [Connection],
       t_object1.NAME        AS [To_Name],
       t_object1.note        AS [To_Note]
      
FROM   t_object

       FULL OUTER JOIN t_connector1
         ON t_object.object_id = t_connector1.start_object_id

      FULL OUTER JOIN t_connector1
         ON t_object1.object_id = t_connector1.end_object1_id;

any ideas?

[Geert Bellekens]

Hi Sean
- t_connector1 does not exist. Use aliases like so
  t_connector c1 on c1.Start_Object_ID = t_object.Object_ID (I always use aliases for all my tables, much easier)
- I you are using a .eap file you'll have to use parentheses for your joins
something like

Code: [Select]

from (((( t_object o
inner join t_package package on o.package_id = package.package_id)
left join t_package package_p1 on package_p1.package_id = package.parent_id)
left join t_package package_p2 on package_p2.package_id = package_p1.parent_id)
left join t_package package_p3 on package_p3.package_id = package_p2.parent_id)
- full outer join why would you want to use a full outer join? Do you want to make a list of all elements and all connectors? I have never needed a full outer join, I would have to look up the syntax for that.

In general the best approach to this is to start small and then expand you query bit by bit. That will allow you to identify the syntax errors quite easily.

Geert

[qwerty]

t_object1 also does not exist in EA databases.

q.

[sean.mcctech]

Thanks, Geert and qwerty,

I was working off a previous query that someone else had built.  Thanks for the suggestions here is the update.  Still getting a syntax error in the Join.  This is supposed to be a custom query for documentation purposes.  The alias in Select corresponds to a table in the document fragment.  Ideally, it would also produce the name of the connected Requirement as well but I will come to that when I get this one working.

SELECT
           t_object.NAME AS [From_Name],
      t_object.NOTES AS [From_Notes]
      
FROM   (t_object Req1
     LEFT JOIN t_connector trace ON Req1.object_id = trace.start_object_id)
   -- Join on t_object(Requirement1) to t_connector(trace) with object_id as common.  Result all requirements with trace

The syntax error I am getting is actually explained in another thread, however, I don't fully understand how to remedy.
DAO.QueryDef[3131] Syntax error in FROM clause
   

[Geert Bellekens]

this works:

Code: [Select]

SELECT req1.NAME AS From_Name, req1.NOTE AS From_Notes
FROM  ( t_object Req1
LEFT JOIN t_connector trace ON Req1.object_id = trace.start_object_id)
Start from there and then add more joins one by one, making sure your syntax stays correct.

Geert

[qwerty]

It also depends on the type of database you're using whether or not certain SQL constructs are supported. I'd guess you're working on an EAP file. So you should keep your hands off from too complex joins. Start with simple WHERE clauses instead (though less performant).

q.

[sean.mcctech]

Yeah, I am working out of an EAP file.  I will see what I can do with a where clause.  Thanks

It also depends on the type of database you're using whether or not certain SQL constructs are supported. I'd guess you're working on an EAP file. So you should keep your hands off from too complex joins. Start with simple WHERE clauses instead (though less performant).

q.

[sean.mcctech]

Thanks Geert,
not sure why but still getting the syntax error in the from clause "too few arguments." 

this works:

Code: [Select]

SELECT req1.NAME AS From_Name, req1.NOTE AS From_Notes
FROM  ( t_object Req1
LEFT JOIN t_connector trace ON Req1.object_id = trace.start_object_id)
Start from there and then add more joins one by one, making sure your syntax stays correct.

Geert

[sean.mcctech]

Ok for the update, below is what works.  Thanks for all the help Geert and q.

I had to change the approach to the connector instead of the object.   The only thing left is to get the notes from the start and end objects.


SELECT
t_Connector.Name AS [Connector],
t_Connector.Connector_Type AS [TYPE],
t_Connector.*,
startObject.name AS [requirement],
endObject.name AS [requirement2]

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 = 'abstraction'
and endObject.Object_Type = 'requirement'       

[qwerty]

You need a little EA voodoo to get the notes. It's required to use t_object.note as norwhatever. For some reason they filter the note field.

q.

[sean.mcctech]

Thanks for looking at it q.
The notes worked with the first try (see below), which was a big surprise.  The last issue I am dealing with now is when the report is run on a package it repeats all relationships for every requirement within that package on each requirement.  Is there some special "package" voodoo that I am missing?

SELECT
t_Connector.Name AS [Connector],
t_Connector.Connector_type AS [TYPE],
t_Connector.*,
startObject.name AS [requirement],
startObject.note as [note],
endObject.name AS [requirement2],
endobject.note As [note2]
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 = 'aggregation'
and endObject.Object_Type = 'requirement'

You need a little EA voodoo to get the notes. It's required to use t_object.note as norwhatever. For some reason they filter the note field.

q.

[Geert Bellekens]

There are a number of macros that you can use in these situations.
#PACKAGEID# in this case.

See https://www.sparxsystems.com/enterprise_architect_user_guide/14.0/model_publishing/custom_sql_fragments.html for more info

Geert

[qwerty]

From a guess: connectors have two ends. So you get a result from the client and the source. That's why you see them twice. You probably have to resort your query so it first gets the connectors as unique set. Honestly, I'd always use a script rather than a complex query. I use query to effectively fetch a large data set and then parse that with a script.

q.