Messages

1

Automation Interface, Add-Ins and Tools / Re: Recursive Query

« on: November 04, 2020, 06:16:25 pm »

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