WITH clause within EA Sparx query

Question

Is it possible to use WITH clauses within an EA Sparx query ?

I wrote this one :

with Activities as

    (
    select t_connector.start_object_id as Object2_id, t_object.* from t_object 
        join t_connector on t_connector.end_object_id=t_object.Object_id
        where t_object.Object_type= 'Activity'

    union

    select t_connector.end_object_id as Object2_id, t_object.*  from t_object  
        join t_connector on t_connector.start_object_id=t_object.Object_id
        where t_object.Object_type= 'Activity'

    )

select * from

    (
    select obj.object_id, count(other.object_id) as 'Activities' from
        t_object obj left join Activities as other on obj.Object_id=other.Object2_id

    group by obj.object_id
    ) as ac 

where object_id in (143306, 143321, 143226, 143326)

and it doesn't return any rows nor throw any errors, while the same query with the WITH-statement placed inline returns the expected results:

select * from
    (
    select obj.object_id, count(other.object_id) as 'Activities' from
        t_object obj left join 
            (
            select t_connector.start_object_id as Object2_id, t_object.* from t_object 
                join t_connector on t_connector.end_object_id=t_object.Object_id
                where t_object.Object_type= 'Activity'

            union

            select t_connector.end_object_id as Object2_id, t_object.*  from t_object  
                join t_connector on t_connector.start_object_id=t_object.Object_id
                where t_object.Object_type= 'Activity'

            )
        as other on obj.Object_id=other.Object2_id

    group by obj.object_id
    ) as ac 

where object_id in (143306, 143321, 143226, 143326)

I know that the SQL engine of EA is a bit tricky (e.g. the query cannot start by a comment).

Is there some tricks here to let WITH clauses working in EA ?

user_2018133 · Answer

EA does some basic checks on the queries. One of them is to check that the query starts with the keyword select.

So, no there is no way to get EA to execute a search query starting with something else then select.

In many circumstances however the WITH clause is used to perform recursive queries e.g. get all elements in a package and all it's subpackages.
For this specific case you can use the macro #Branch# which will be translated to a comma separated list of packageID's of the currently selected package and all of it's subpackages recursively.

WITH clause within EA Sparx query

1 Answers