How to get the children of Virtual Documents in Documentum

Question

I am trying to get the children of a particular virtual document by using the query below

select * from dm_document in document ID('virtual document id') descend with any r_version_label='CURRENT' 

I am able to get the children for a particular virtual document, but I want all the children (only current version) of all the virtual documents by a single dql query

Can anyone help me with the dql query to get all childs of all virtual documents present in the docbase?

And, what does direct and indirect components of virtual document mean?

Update: The query which i am using is

select e.*,C.r_object_id as folder_id,C.r_folder_path from dm_folder_r C,
(
select A.*,A.i_folder_id from dm_document A,
(
 SELECT r_object_id,object_name FROM dm_document 
     WHERE i_chronicle_id 
    IN (select component_id,version_label from dmr_containment where parent_id in (select r_object_id from dm_document where r_is_virtual_doc=1 or r_link_cnt>0))
   )d 
where d.r_object_id=A.r_object_id 
)e where e.i_folder_id=C.r_object_id and C.r_folder_path is not null  

If a component in virtual document is also a virtual document (nested virtual document), does dmr_containment gives the components of nested virtual document? I mean does dmr_containment gives all the components up to the leaf level(highest depth) or we have to use descend in the query?

If we have to use descend then where do we have to include the descend in the dql query which I am using?

Please help me.

Thanks in advance.

Answer 1

The problem with this request is that there might be virtual documents having a child referenced by a non-current ID. But you can still get the list...

Try something this:

SELECT 
  pa.r_object_id, ch.r_object_id, cu.r_object_id
FROM
  dm_document pa,
  dm_document (ALL) ch ,
  dm_document cu,
  dmr_containment r
WHERE
  pa.r_is_virtual_doc = 1
  and r.parent_id = pa.r_object_id
  and r.component_id = ch.r_object_id
  and cu.i_chronicle_id = ch.i_chronicle_id

In most cases you will get the same value for 2nd and 3rd column since the child will be linked to current version, but sometimes you can get a different one. For the 1st and 3rd column you will also get same values for some of the records since the first component of a VD is the parent document. Really you should look into the dmr_containment object table and getting to know this to get your desired output.

Hope this helps.