Developing a Traceability SQL

[tagem0]

Hello,
I have been asked to write an sql that would show related elements of an element, similar to how the Hierarchy Window works in EA. So basically if I have the following relationships (e.g agregations of requirements)
A -> B,  B -> C, C -> D, D -> E.  Then if I am asked to show the related elements of C, then the SQL should give me A, B, D and E.

Has anyone got a script or may be any ideas on how to accomplish this. The goal is to use the data from the sql to produce an RTF document.

Thanks

Tagem

[Geert Bellekens]

I guess you just forgot to include "C" in the desired results?

In theory you would need a recursive query to be sure you get the whole hierarchy. Unfortunately recursive queries are not (yet) available in some of the common databases EA works on.

In practice you could probably do with a regular query, given that you have an idea of the deepest level of your hierarchy.

You will basically have to have as many joins to the t_object (if that is where the requirements are stored) as you have levels.
Since you want to find those related items through a relation, you'll have to include the t_connector in the joins as well.

I don't have anything ready to use for you, but I hope this already helps a little.

Geert

[tagem0]

Thanks Geert.
However I am struggling to do this recursive queries or Transitive Closure in Access as our EA is based on Access. I am an Oracle guy and in Oracle I would have a choice of Oracle SQL or stored procedure using a cursor to loop through.
I am currently learning C#. Is it possible to do this in C# and use it in EA and it so how?. The idea is to use it in search and eventually produce an RTF document of the search results.

Thanks for your anticipated help.

Tagem

.

[Geert Bellekens]

Tagem,

Yes, doing it in C# is an option. Search for addin-search in the help files.
Just be careful what you want to search using C# because it will be a lot (as in orders of magnitude) slower then a well written sql query.

Geert

PS. You know EA can be set up to run on top of Oracle?

[tagem0]

Hi Geert,
Thanks for your help so far.

I decided to go with your recommendation to do this via sql. After some tedious effort I came up with an sql query developed in Access 2007 that produces the information that I am after.
However when I took this over to EA to create a Custom SQL search, it came up with error  “DAO. QueryDef[3131]  Syntax error in FROM clause. The EA editor does not seem to like Inline views.

An example of what I am trying to do is below.

Select t_object.name ............
from t_object, (
                       Select end_object_id
                        From t_connector
                      where start_object_id = 7419
                    ) temp INNER JOIN  t_object t ON temp.end_object_id = t.Object_ID

It works fine in Access. My version of Ea is 7.5.848 and the Database version is 4.01.


Do you have any ideas on how I could go round this?


Thanks

[tagem0]

I have now set EA to Use JET 4.0.

My sql now works

Thanks

[Geert Bellekens]

Hmm yeah, that was what I was going to suggest

Geert

[mvngomes]

Hi,

Sorry to bring up and old topic again but this is exacly what I am trying to do now.

I'm looking for an SQL query to find the transitive closure of an element - that is, all other elements a certain element relates to. In this case, the direction of an association does not matter, eg. A -> B -> C or A <- B <- C still would yield the same result, A is related to itself, B and C.

The query posted here does almost that, with the exception that when the element in question does not contain an association in which it is the source, nothing is returned.

I am trying to get a query that works in SQL because doing that using the EA repository object model is pretty slow - it's easy to write a method to do that, but the performance was not the best.

A last comment: can the query be written using only the t_connector table, or one of its views? Since the query posted here will return also the element's folders, diagrams and etc, which in my case do not matter.

Thanks!

[Geert Bellekens]

You'll probably want something like

Code: [Select]

select * from t_connector c
join t_object source_object on c.Start_Object_ID = source_object.Object_ID
join t_object target_object on c.End_Object_ID = target_object.Object_ID
where c.Connector_Type = 'Association'
and (source_object.Name = '<Search Term>' OR target_object.Name = '<Search Term>')
geert

[mvngomes]

Thanks for the help! The query works fine, except that it is not transitive - in the case of having A -> B -> C, and looking for what's connected to A, I'd get A and B, C is missing. Any thought on how I can achieve that?

[Geert Bellekens]

Add another left join with t_connector and then left joins with t_object, so something like this:

Code: [Select]

select * from t_connector c
join t_object source_object on c.Start_Object_ID = source_object.Object_ID
join t_object target_object on c.End_Object_ID = target_object.Object_ID
left join t_connector c2 on
      (
            (c2.End_Object_ID = source_object.Object_ID and source_object.Name <> '<Search Term>'
            or
            c2.Start_Object_ID = source_object.Object_ID and source_object.Name <> '<Search Term>'
            )
      or
            (c2.End_Object_ID = target_object.Object_ID and target_object.Name <> '<Search Term>'
            or
            c2.Start_Object_ID = target_object.Object_ID and target_object.Name <> '<Search Term>'
            )
      )
left join t_object source_object2 on c2.Start_Object_ID = source_object2.Object_ID
left join t_object target_object2 on c2.End_Object_ID = target_object2.Object_ID
where c.Connector_Type = 'Association'
and (source_object.Name = '<Search Term>' OR target_object.Name = '<Search Term>')

[mvngomes]

Thanks again Geert!
There's some error in the sintax but I'll try to figure it out...

[Geert Bellekens]

Yes,

My example is MS SQL Server syntax.
If you use .eap files you'll have to use MS Access syntax.

Geert

[mvngomes]

I figured I need to change the "JOIN" to "INNER JOIN", but still get an error in the FROM clause. Everything seem to be ok, the parenthesis match, so as for right now I'm still stuck...

[Geert Bellekens]

I'm getting "join expression not supported" when i try it on an eap file.
Apparently left joins are not supported

Geert