10 Join Limit

Is there a way around the 10 join limit in DQL?

select * from nike_asset  where r_object_id in (select dr1.child_id
from dm_relation dr1, dm_relation dr2, dm_relation dr3, dm_relation
dr4, dm_relation dr5, dm_relation dr6, dm_relation dr7, dm_relation
dr8, dm_relation dr9, dm_relation dr10, dm_relation dr11, dm_relation
dr12, dm_relation dr13, dm_relation dr14, dm_relation dr15, dm_relation
dr16, dm_relation dr17, dm_relation dr18, dm_relation dr19, dm_relation
dr20  WHERE dr1.child_id = dr2.child_id AND
dr1.parent_id='08018c848008b873' AND dr2.parent_id='08018c848008b874'
AND dr2.child_id = dr3.child_id AND dr2.parent_id='08018c848008b874'
AND dr3.parent_id='08018c848008b89e' AND dr3.child_id = dr4.child_id
AND dr3.parent_id='08018c848008b89e' AND
dr4.parent_id='08018c848008b89f' AND dr4.child_id = dr5.child_id AND
dr4.parent_id='08018c848008b89f' AND dr5.parent_id='08018c848008b881'
AND dr5.child_id = dr6.child_id AND dr5.parent_id='08018c848008b881'
AND dr6.parent_id='08018c848008b882' AND dr6.child_id = dr7.child_id
AND dr6.parent_id='08018c848008b882' AND
dr7.parent_id='08018c848008b883' AND dr7.child_id = dr8.child_id AND
dr7.parent_id='08018c848008b883' AND dr8.parent_id='08018c848008b84d'
AND dr8.child_id = dr9.child_id AND dr8.parent_id='08018c848008b84d'
AND dr9.parent_id='08018c848008b84e' AND dr9.child_id = dr10.child_id
AND dr9.parent_id='08018c848008b84e' AND
dr10.parent_id='08018c848008b84f' AND dr10.child_id = dr11.child_id AND
dr10.parent_id='08018c848008b84f' AND dr11.parent_id='08018c848008b873'
AND dr11.child_id = dr12.child_id AND dr11.parent_id='08018c848008b873'
AND dr12.parent_id='08018c848008b874' AND dr12.child_id = dr13.child_id
AND dr12.parent_id='08018c848008b874' AND
dr13.parent_id='08018c848008b89e' AND dr13.child_id = dr14.child_id AND
dr13.parent_id='08018c848008b89e' AND dr14.parent_id='08018c848008b89f'
AND dr14.child_id = dr15.child_id AND dr14.parent_id='08018c848008b89f'
AND dr15.parent_id='08018c848008b881' AND dr15.child_id = dr16.child_id
AND dr15.parent_id='08018c848008b881' AND
dr16.parent_id='08018c848008b882' AND dr16.child_id = dr17.child_id AND
dr16.parent_id='08018c848008b882' AND dr17.parent_id='08018c848008b883'
AND dr17.child_id = dr18.child_id AND dr17.parent_id='08018c848008b883'
AND dr18.parent_id='08018c848008b84d' AND dr18.child_id = dr19.child_id
AND dr18.parent_id='08018c848008b84d' AND
dr19.parent_id='08018c848008b84e' AND dr19.child_id = dr20.child_id AND
dr19.parent_id='08018c848008b84e' AND dr20.parent_id='08018c848008b84f'

[DM_QUERY2_E_TOO_MANY_TYPES_IN_JOIN]error: "Your query has too many types in the FROM clause."

Find more posts tagged with

Comments

RalfKahrl

Hi Ken,

are those object_ids fixed?

If they are, you could try and create a custom view in your database to retrieve your object_id and reduce your query like this:

select * from nike_asset where r_object_id in (select r_object_id from dbo.mycustomrelationview);

Ralf

Francois Dauberlieu

The thing is that there is a limit of 10 collections (used by DQL queries) opened at the same time per session and looking at your query, there are so many joins that the server needs more than that.

One way to proceed, which I find faster than DQL is to create a view in your database doing this statement (obviously, you'll have to use the real Dctm table name ****_s, ****_r) in it and then register it as you would for a table. Then you can just query this 'registered' table.

My 2 cents

kdomen

Thanks! I appreciate it!

Sent from my iPhone

On Nov 6, 2009, at 2:23 AM, "Francois Dauberlieu" <ecn-

kdomen

those object ids are not fixed.

thanks!

ken

Francois Dauberlieu

How do you get them ?

Is it possible to get them in a DQL/SQL statement ? If yes, then you could still build a view, or even multiple but then register in Documentum only the one giving the final result ?