Hi Experts,
I have a requirement to create a fixed DQL which is able to query the children of all the virtual documents in the system. The fields I need in the result set are object ID, version label and name of the child as well as the object Id,version label and name of the parent.
Since my DQL has to be a fixed DQL (one which is executed by the click of a menu item and displays the results on the screen) I cannot use the IN DOCUMENT clause.
I tried to create a query like below but this is not returning correct results because I think there is no WHERE clause which defines the relation between parent and child.
SELECT child.object_name as object_name,
par.object_name as parent_name,
child.r_object_id as emc_id,
par.r_object_id as parent_id,
FROM asset_doc(all) child,dmr_containment dc, asset_doc(all) par
WHERE child.i_chronicle_id = dc.component_id
AND dc.parent_id = par.r_object_id
This query many more results than there should be.
Can someone suggest how can I achieve the req.
