EA Sparx 查询中的WITH 子句

是否可以在 EA Sparx 查询中使用WITH 子句？

我写了这个：

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)

它不会返回任何行，也不会抛出任何错误，而内联放置WITH语句的相同查询会返回预期结果：

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)

我知道 EA 的 SQL 引擎有点棘手（例如查询不能从注释开始）。

这里有一些技巧可以让WITH子句在EA中工作吗？

EA 对查询进行一些基本检查。其中之一是检查查询是否以关键字开头select.

因此，没有办法让 EA 从其他内容开始执行搜索查询select.

然而在许多情况下WITH子句用于执行递归查询，例如获取包及其所有子包中的所有元素。
对于这种特定情况，您可以使用宏#Branch#它将被递归地转换为当前所选包及其所有子包的 packageID 的逗号分隔列表。