Firstly, sorry for the long post.
Our model has standard classes with operations but we also have activity parameters representing the same operations. I am trying to locate missing ones via SQL.
As a test example, we have the following operations (listed as <class>::<operation>)
- c1::MyOperation1
- c1::MyOperation2
- c2::MyOperation1
- c2::MyOperation2
- c2::MyOperation3
- c2::MyOperation4
and the following activity parameters (listed as <activity>::<activityparameter>)
- c1::MyOperation1
- c1::MyOperation2
- c1::MyOperation3
- c2::MyOperation1
- c2::MyOperation2
The errors are as follows
- Class/operation c2::MyOperation3 has no corresponding activity/activityparameter
- Class/operation c2::MyOperation4 has no corresponding activity/activityparameter
- Activity/activityparameter c1::MyOperation3 has no corresponding class/operation
The first 2 are what I want my SQL query to detect. The 3rd one will be detected by a similar query (when I get the first one working). I have performed earlier queries that detected classes without identical named activities.
Therefore, the expected results from any query are
- c2::MyOperation3
- c2::MyOperation4
I have tried to write an SQL query that detects this but keep getting stuck. The query below is where I started from.
SELECT *
FROM
(((
t_object o_class
INNER JOIN t_object o_activity ON
( o_activity.name = o_class.name
AND
o_class.object_type = 'class'
AND
o_activity.object_type = 'activity'
)
)
INNER JOIN t_operation o_operation ON o_operation.object_id = o_class.object_id)
LEFT JOIN t_object o_actparam ON
( o_actparam.name = o_operation.name
AND
o_actparam.object_type = 'activityparameter'
AND
o_actparam.parentid = o_activity.object_id
)
)
WHERE
o_actparam.name is NULL
The above aims to get a class, then an activity with the same name, then all operations belonging to the class, then
for each operation
try and find an activity parameter in this activity with the same name. Any that didn't match should return NULL (since it is a LEFT JOIN) and so the WHERE statement shows the operations that didn't have a related activityparameter, i.e. the errors.
The above does not work; I get a "JOIN expression not supported".
If I take out the "o_actparam.parentid = o_activity.object_id" then it returns no results at all.
This is clearly wrong.If I change the LEFT JOIN to
LEFT JOIN t_object o_actparam ON
( o_actparam.name = o_operation.name
AND
( o_actparam.object_type = 'activityparameter'
OR
o_actparam.object_type is NULL
)
)
then I get the result
It has failed to find
c2::MyOperation3. This is because operation c2::MyOperation3 matches activityparmater MyOperation3 (even though that belongs to class/activity c1). The comparison ignores the class/activity.
If I change the WHERE (and keep the above LEFT JOIN) to
WHERE
o_actparam.parentid <> o_activity.object_id
OR
o_actparam.parentid is NULL
then I get the result
- c1::MyOperation1
- c1::MyOperation2
- c2::MyOperation1
- c2::MyOperation2
- c2::MyOperation3
- c2::MyOperation4
It is now finding lots of wrong items since the same activityparameter name exists in different activities. The WHERE is too late to throw away items, however, putting the parentid in the LEFT JOIN gave me the "JOIN expression is not supported" error.
Is someone able to help me here?
Thanks
Darren
