Recursive SQL Query

[Vinnie]

I want to return all the child packages (recursively) of one package in the Project Search using SQL.

Doing some searching, I found out that it is possible. so I have adapted the example SQL that i've found to my purposes. The query that I am using is the following:

Code: [Select]

with t1 (Parent_ID, Package_ID) as (
select root.Package_ID, root.Parent_ID from t_package root where root.Parent_ID = 378
union all
select p.Package_ID, p.Parent_ID from t_package p, t1 where p.Parent_ID = t1.Package_ID
) select Package_ID from t1
But I am getting no result neither any error. Does anybody knows if it is possible to do something like that? Or is my query incorrect?

Thanks!

[qwerty]

The SQL syntax differs amongst various RDBMS. Have a look into %APPDATA%\Sparx Systems\EA\dberror.txt

q.

[OpenIT Solutions]

Also check out the #Branch# tag that you can add to your where clause, ie

SELECT * FROM t_object WHERE package_id IN (#Branch#)

Also see http://www.sparxsystems.com/uml_tool_guide/modeling_tool_features/creating_filters.htm

[Vinnie]

Thanks for the responses!

The #Branch# tag actually worked just fine (didn't know about it).

I was testing the my SQL queries to include in a RTF documentation that I was creating.

I also found out that the ## tags do not work at the SQL tab in the Model Search, but that is not true for SQL queries in an RTF Template, right?

[OpenIT Solutions]

No you can't use the tags when writing direct sql in the sql tab. You can if you create a model search. That model search will work with the tags in a model document - not tested fragments in 10 yet - but i assume it will work fine in them as well.

[Vinnie]

So, I have created a template fragment with my custom SQL and custom fields and a master template in which I've just added the package tag and the template fragment inside it.

When trying to generate the document i got the following:

DAO.Database [3075]

Syntax error in date in query expression 'd.Package_ID IN (#Branch#) AND d.Diagram_ID = do.Diagram_ID AND o.Object_ID = do.Object_ID'.

Just so you know, the query worked just fine in the model search. Does it means that RTF templates do not support tags?

The SQL used is the following, if necessary

Code: [Select]

select a.Name as aname, i.Name as iname
from t_object a, t_object i, t_connector c
where c.Start_Object_ID = a.Object_ID AND c.End_Object_ID = i.Object_ID AND c.Stereotype = "Dependency Link" AND a.Stereotype = "Actor" AND
a.Object_ID in (select o.Object_ID
                  from t_object o, t_diagram d, t_diagramobjects do
                  where d.Package_ID IN (#Branch#) AND d.Diagram_ID = do.Diagram_ID AND o.Object_ID = do.Object_ID
                  group by o.Object_ID) AND
i.Object_ID in (select o.Object_ID
                  from t_object o, t_diagram d, t_diagramobjects do
                  where d.Package_ID IN (#Branch#) AND d.Diagram_ID = do.Diagram_ID AND o.Object_ID = do.Object_ID
                  group by o.Object_ID)

[qwerty]

Actually I don't know but it could well be. EA is quite inconsistent in its implementation. However, I see no reason why #branch# should not work with RTF templates. If it does not work, you should send a bug report (link bottom right) and switch over to the manual SQL. Geert and Helmut have published several solutions here (you might spent some time with this forum's famous search).

q.

[Vinnie]

Thanks qwerty, I've already sent a bug report. Should I expect an answer from them?

BTW, by manual SQL you mean not use tags?

I will search the forum now for some solution as you said, will post here if I find anything.

[qwerty]

You should not put much hope in expecting an answer :-/ Sometimes you get a reply and sometimes you don't.

The manual way means that you have to hand code the recursion into your SQL. There are different ways (mainly depending on your RDBMS' capabilities). I hope you are lucky with the search. Try with time range one year. That still takes several minutes but yields quite some results if your search string is good. Alternatively you can try searching the EA google groups (don't have the address at hand).

q.

[g.makulik]

Alternatively you can try searching the EA google groups (don't have the address at hand).

Here they are (see Wyatts post): Improve speed of forum searches.

That's what I meant should be placed in an easy to spot and available for referencing 'important' topic in the general board ...

HTH
Günther

[qwerty]

There isn't even a link to their own community

q.