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.

select  

   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

 

 

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s