Hi All,
I want to fetch list of all documents and the foldernames for dm_documents that are linked to multiple folder. Can anyone help me on dql query to find this.
Thanks,
Rajeev
Try this (I havent verified it):
select object_name from dm_sysobject where count(i_folder_id)>1
You can try this select object_name from dm_sysobject where r_link_cnt > 1
the count folder ID did not work for me
Thanks for your response. Both the queries work fine but I need to fetch document object name along with the folder names to which these documents are linked. So result should display documents and folder names by joining dm_folder and dm_document based on the condition that document is linked to more than 1 folder.
This is an old one, but sharing my learnings ...
Was looking for count of document objects with more than one folder link.
select count(*) from dm_document where count(i_folder_id) > 1;fails because i_folder_id is repeating
select count(*) from dm_document where r_link_cnt > 1;does not work for documents. r_link_cnt for doc objects only counts links to virtual docs. for dm_folder objects, it provides number of objects linked to folder (backwards of what I was looking for).
I resort to querying as registered table when working with repeating attributes. May need to connect as Install Owner for this to work.
This should give count of distinct objects, including all versions (and possibly 'deleted' objects!)select count(distinct r_object_id) from dm_dbo.dm_document_rv where i_folder_id > ' '
This should give count of all versions with more than one folder link. select sum(count(distinct r_object_id)) from dm_dbo.dm_document_rv where i_folder_id > ' ' group by r_object_id having count(i_folder_id) > 1;
This should give count of all versions with one folder link or less (technically, all objects should have at least one link). select sum(count(distinct r_object_id)) from dm_dbo.dm_document_rv where i_folder_id > ' ' group by r_object_id having count(i_folder_id) <= 1;
You can replace the sum function with any attribute in repeating tables. Example:
select distinct r_object_id from dm_dbo.dm_document_rv where i_folder_id > ' ' group by r_object_id having count(i_folder_id) > 1;
The counts that were returned did not quite add up, but very close. I spot-checked a bunch of results from both queries, and it appeared to work. The first query only returned objects with multiple i_folder_id values; the second only returned objects with one i_folder_id value. Did not confirm all results.
Yes, if you want any single-valued results in select, you have to join on dm_dbo.dm_document_sv by r_object_id. Will leave that fun to others.
Moved to Documentum Developer Community.
Apologies. But I did not post it. I just responded to pre-existing post
that I found that came up in Powerlink search.
========================
Steven Savini
SS Solutions & Consulting LLC
Office: 302-737-2446
Cell: 302-668-8451
steve.savini@usa.dupont.com
Jerry Silver <emc-community-network@emc.com>
01/26/2012 11:52 AM
Please respond to
jive-479862597-3fds-2-cm0u@community.emc.com
To
Steve Savini/Contractor/AE/DuPont@DuPont
cc
Subject
New message: "dql to find folder names and document object
names for documents that are linked to multiple folders"
EMC Community Network
Re: dql to find folder names and document object names for documents that
are linked to multiple folders
created by Jerry Silver in Documentum - View the full discussion
Please Read - DO NOT POST TECHNICAL QUESTIONS TO THE DOCUMENTUM CONNECT
FORUM!
Reply to this message by replying to this email -or- go to the message on
ECN
Start a new discussion in Documentum by email or at ECN
This communication is for use by the intended recipient and contains
information that may be Privileged, confidential or copyrighted under
applicable law. If you are not the intended recipient, you are hereby
formally notified that any use, copying or distribution of this e-mail,
in whole or in part, is strictly prohibited. Please notify the sender by
return e-mail and delete this e-mail from your system. Unless explicitly
and conspicuously designated as "E-Contract Intended", this e-mail does
not constitute a contract offer, a contract amendment, or an acceptance
of a contract offer. This e-mail does not constitute a consent to the
use of sender's contact information for direct marketing purposes or for
transfers of data to third parties.
Francais Deutsch Italiano Espanol Portugues Japanese Chinese Korean
http://www.DuPont.com/corp/email_disclaimer.html
Try,
select distinct d.object_name as Document_Name, d.subject, d.a_content_type, d.r_object_id as d_id,f.object_name as Folder_Name,f.r_object_id as f_id, f.r_folder_path as Parent_Folder from dm_document d, dm_folder f where d.r_link_cnt >1 and any d.i_folder_id = f.r_object_id order by f.r_object_id, d.r_object_id, f.object_name, d.object_name ENABLE (ROW_BASED)
Cheers Niall
DQL to find objects with multiple folder links; includes object_name. in fact, can include any property in select clause. requires superuser access to use the DCTM view (or you'd have to register the view and provide appropriate permissions on the dm_registered object to allow query without superuser access; but, that would not be recommended for security reasons).
only CURRENT:
select r_object_id, object_name from dm_document where r_object_id in (select distinct r_object_id from dm_dbo.dm_document_rv where i_folder_id > ' ' group by r_object_id having count(i_folder_id) > 1)
all versions:
select r_object_id, object_name from dm_document (all) where r_object_id in (select distinct r_object_id from dm_dbo.dm_document_rv where i_folder_id > ' ' group by r_object_id having count(i_folder_id) > 1)
