Sparx freezes while running SQL Query

[sravang]

Hello,

I am facing some problem with SQL query while trying to process relations. Please go through the queries mentioned below and help me if possible.

select * from  t_object tob inner join t_connector tc on tc.start_object_id = tob.object_id or tc.end_object_id = tob.object_id where  (ParentID in (select Object_ID from t_object tob left join t_package tp on tob.Package_ID=tp.Package_ID where Object_Type = 'Part' and parentID = 7719) or ParentID = 7719)
This is the query which hangs.
Subquery which is  select Object_ID from t_object tob left join t_package tp on tob.Package_ID=tp.Package_ID where Object_Type = 'Part' and parentID = 7719 - Returns 120397,120399
And query without subquery which is -  select * from  t_object tob inner join t_connector tc on tc.start_object_id = tob.object_id or tc.end_object_id = tob.object_id where ParentID = 7719 also returns 7 connector rows.
And In the end if I try to execute the same query with values in it - select * from  t_object tob inner join t_connector tc on tc.start_object_id = tob.object_id or tc.end_object_id = tob.object_id where  (ParentID in (120397,120399) or ParentID = 7719)
It works

I don't get why it doesn't work with subquery

Thanks,
Sravan

[Geert Bellekens]

You are reusing the alias "tob" in your query and the subquery which can't be good.

I also don't understand what you are trying to do.
You are looking for elements owned by an element with ID 7719
But you also join t_connector and t_package, but it's not clear what you want to do with these.

Can you explain in plain words what you are looking for?

Geert

PS. Maybe it's also a better idea to create a new topic instead of "hijacking" this one.

[sravang]

I am trying to find all the relations between the sub elements of a particular element.

Consider a Block which has parts and parts have ports embedded on them.

There are links between part to part and also from port to port.

I want to find the relations between these parts and ports.

So just want to find relations involved inside that block.

PS. Also tried this query without alias - "select * from  t_object tob inner join t_connector tc on tc.start_object_id = tob.object_id or tc.end_object_id = tob.object_id where  (ParentID in (select Object_ID from t_object where Object_Type = 'Part' and parentID = 7719) or ParentID = 7719)" but it still hangs.

Thanks,
Sravan

[Geert Bellekens]

You mean something like this?

Code: [Select]

select * from t_object so
inner join t_connector c on c.Start_Object_ID = so.Object_ID
inner join t_object eo on eo.Object_ID = c.End_Object_ID
inner join t_object spo on spo.Object_ID = so.ParentID
inner join t_object epo on epo.Object_ID = eo.ParentID
where 1 = 1
and 7719 in (spo.Object_ID, spo.ParentID)
and 7719 in (epo.Object_ID, epo.ParentID)
so = start object
eo = end object
c = the relation between start object and end object
spo = start parent object
epo = end parent object

where clause checks if both epo as spo are either 7719, or have 7719 as parent.
This makes the so and eo one level or two levels below object 7719

Geert

PS. You might need to a set of parentheses for each join to get this query to work on .eap files

[qwerty]

You should setup a "build my query" service where you can charge all those guys you helped out so far. You'd be a rich man now if you had...

q.

[Geert Bellekens]

You should setup a "build my query" service where you can charge all those guys you helped out so far. You'd be a rich man now if you had...

q.

[sravang]

The above code worked but it had some relations less than the other query which I was using will look into that.

Thanks,
Sravan