Sparx Systems Forum
Enterprise Architect => Automation Interface, Add-Ins and Tools => Topic started by: royvanmarrewijk on February 23, 2017, 01:42:32 am
-
Hi,
I am trying to query the complete decomposition of a given element (the element itself and all it's childs, and the childs its childs) to seperate rows and I would like to do this recursively. To do this I am thinking of using the WITH statement but I can't test this because the SQL Sqratch Pad doesn't execute the query if it doesn't start with select.
With hierarchy()
AS
(
)
SELECT *
FROM hierarchy
Can anyone tell me how to run this query or how to create a recursive query and start with Select.
Roy
-
I don't think that's possible. AFAIK recursive queries always begin with "with" and thus are not accepted by EA.
What I usually do is go deep enough. In most cases I've seen that was up to 10 levels deep.
If it is more then that you better write a script. (that you can use as search as well)
Geert
-
This sounds like SQL has left behind EA.
Presumably it is checking that the statement begins SELECT so that you don't "UPDATE", but I think a feature request is in order to update EA in line with the new SQL syntax.
Presumably you can't do
SELECT * FROM ( WITH ... SELECT ... )
(I can't check as I'm using JETEngine which I don't think supports that kind of advanced SQL anyway)
-
Hello,
the EA way to SQL is to support a lot of different databases. That's not easy and of course, a lot of opinions how to do it best.
You always can speak to your database directly. You can get a connection string and go from that.
If you want to change the current EA behavior make a change request.
Kind regards,
Helmut
-
Because of you are trying to use the "WITH" statement, I assume you have your repository in a real DB like MySQL, Oracle,…
If so, what you could do is defining your “With-SQL” in your DB as a View and then “SELECT” your view within EA.
-
This sounds like SQL has left behind EA.
Presumably it is checking that the statement begins SELECT so that you don't "UPDATE", but I think a feature request is in order to update EA in line with the new SQL syntax.
Presumably you can't do
SELECT * FROM ( WITH ... SELECT ... )
(I can't check as I'm using JETEngine which I don't think supports that kind of advanced SQL anyway)
i can confirm that solution. We are using MySQL 8.0 for our repository. The following example works from inside EA (e.g. Search,Modelview,..)
select * from (
WITH RECURSIVE cte_tree_down (Parent_ID,Name,Package_ID,Depth)
AS (
SELECT p.Parent_ID, p.Name, p.Package_ID, 1 as Depth
FROM t_package p
WHERE p.ea_guid = '{80315719-C3F0-4375-B016-C788C8B61231}'
UNION ALL
SELECT c.Parent_ID, c.Name, c.Package_ID,Depth+1 as Depth
FROM t_package c
INNER JOIN cte_tree_down p on c.Parent_ID = p.Package_ID
)
select o.Name from t_object o
join cte_tree_down pt on o.Package_ID = pt.Package_ID
where
o.Object_Type = 'Requirement'
) as foo
just replace the GUID with the one of the subtree root package
BR Thomas