General DQL queries for Documentum Admin management job.
1- How to count documents in all the user's cabinet
select c.object_name, count(*) as total_doc_count
from dm_cabinet c, dm_sysobject (ALL) s, dmr_content dc
where s.i_cabinet_id = c.r_object_id
and any dc.parent_id = s.r_object_id
group by c.object_name order by 1;
OR
SELECT cab.object_name, count(*) as TotalDocs
FROM dm_document doc, dm_cabinet cab
WHERE cab.r_object_id = doc.i_cabinet_id
GROUP BY cab.object_name;
2-How to count the number of documents which are linked to outside the cabinet?
select distinct r_object_id, object_name, i_reference_cnt from dm_document (all)
where r_object_id in
(select r_object_id from dm_document where folder('/CabinetA', descend)
group by r_object_id having count(i_folder_id) > 1)
and r_object_id not in
(select r_object_id from dm_document where i_folder_id not in (
select r_object_id from dm_folder where r_folder_path not like '/CabinetA/%'))
enable(row_based);
OR
select distinct r_object_id, object_name from dm_document (all)
where r_object_id in
(select r_object_id from dm_document where i_cabinet_id = 'CabinetA_id'
group by r_object_id having count(i_folder_id) > 1)
and r_object_id not in
(select r_object_id from dm_document where i_folder_id not in (
select r_object_id from dm_folder where r_folder_path not like '/CabinetA/%'))
enable(row_based);
SELECT cab.object_name, count(*) as TotalDocs
FROM dm_document doc, dm_cabinet cab
WHERE cab.r_object_id = doc.i_cabinet_id
GROUP BY cab.object_name;
2-How to count the number of documents which are linked to outside the cabinet?
select distinct r_object_id, object_name, i_reference_cnt from dm_document (all)
where r_object_id in
(select r_object_id from dm_document where folder('/CabinetA', descend)
group by r_object_id having count(i_folder_id) > 1)
and r_object_id not in
(select r_object_id from dm_document where i_folder_id not in (
select r_object_id from dm_folder where r_folder_path not like '/CabinetA/%'))
enable(row_based);
OR
select distinct r_object_id, object_name from dm_document (all)
where r_object_id in
(select r_object_id from dm_document where i_cabinet_id = 'CabinetA_id'
group by r_object_id having count(i_folder_id) > 1)
and r_object_id not in
(select r_object_id from dm_document where i_folder_id not in (
select r_object_id from dm_folder where r_folder_path not like '/CabinetA/%'))
enable(row_based);
Comments (0)
Post a Comment