DQL: How to find Documentum folders with 0 (zero) documents in any subfolders

I was recently asked to find all custom folders in a cabinet which may have other nested folders, but no documents. In other words, folders can have other folders, but not documents.

In order to find empty folders, with no documents or sub-folders, is straightforward:

select * from dm_folder where i_reference_cnt=0

But what we are looking for are folders which may contain other folders, but none of the sub-folders should have a document.

I came up with the following query. Keep in mind that this query can be improved upon, for example by using joins. I only need to run it once so I have no need for performance based improvements at this time.


   r_object_id, object_name, r_folder_path, r_creation_date, r_modify_date

from dm_folder //or custom dm_folder subtype

where r_object_id not in   //Returns all folders which are not in the list of ancestor folders (folder hierarchy) as returned below


    select distinct (i_ancestor_id) from dm_folder where r_object_id in   //Returns all ancestor folders in the hierarchy of the parent folders below


        select distinct(i_folder_id) from dm_document WHERE <your custom where clause>   //Returns all parent folders which contain documents 



and any r_folder_path is not nullstring

and folder('/Background Investigation', descend)

enable (ROW_BASED)  //Needed when querying repeating attributes such as r_folder_path above




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s