Tried to create a search using the following SQL, however, EA does not seem to understand the syntax 'with ....' for a in line table, and won't produce any results, nor generate any error. I had to rewrite with less efficient code and with a fixed number of layers of the package folders instead of unlimited reclusive package structure when writing with 'with... syntax':
[highlight]with package_recursive (name,package_id)
as
( select t.name,t.package_id from t_package t where t.package_id=14595 --t.name = 'IPA Physical Data Model Current Trunk'
union all
select t2.name,t2.package_id from t_package t2 inner join package_recursive as pcur on pcur.package_id = t2.parent_id
)[/highlight]
select op.ea_guid as CLASSGUID, 'Operation' as CLASSTYPE, c.sourcerole as "FromConstrName",o1.name as "FromTable", p1.name as "FromPkg", c.destrole as "ToConstrName", o2.name as "ToTable", p2.name as "ToPkg"
from package_recursive p1 inner join t_object o1 on o1.package_id=p1.package_id inner join t_connector c on o1.object_id=c.start_object_id
inner join t_object o2 on o2.object_id=c.end_object_id inner join package_recursive p2 on o2.package_id=p2.package_id left join t_operation op on c.sourcerole=op.name
where o1.stereotype='table' and o2.stereotype='table'
and (c.sourcerole+','+c.destrole) not in (select cp1.name+','+cp2.name from
(select tt1.name, stuff((SELECT ',' + to1.type FROM t_operationparams as to1 where to1.operationid=tto1.operationid order by to1.pos FOR XML PATH('')),1,1,'') as frmKeyColType
from t_operationparams as tto1, t_operation as tt1 where tto1.operationid=tt1.operationid and tt1.name=c.sourcerole and tt1.object_id=c.start_object_id) cp1,
(select tt2.name, stuff((SELECT ',' + to2.type FROM t_operationparams as to2 where to2.operationid=tto2.operationid order by to2.pos FOR XML PATH('')),1,1,'') as toKeyColType
from t_operationparams as tto2, t_operation as tt2 where tto2.operationid=tt2.operationid and tt2.name=c.destrole and tt2.object_id=c.end_object_id) cp2
where cp1.frmKeyColType=cp2.toKeyColType)
Any idea whether this is an EA bug? Any workaround?
