Recursive Query

[royvanmarrewijk]

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.

Code: [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

[Geert Bellekens]

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

[Screwtape]

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

Code: [Select]

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)

[Helmut Ortmann]

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

[PeterHeintz]

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.

[tomtrath]

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

Code: [Select]

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,..)

Code: [Select]

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