Home Documentum

DQL Query to count specific folders


I'm trying to use the DQL Query below to count files but i believe it will only count all the files that are in "Folder A".  Can you provide a query that will let me count the files within "Folder A" through "Folder Z" (they are all in the same directory) and then stop?

select count(*) from dm_document where FOLDER('/Cabinets/Folder/Folder/Folder/Folder A ', descend)

Thank you,



  • What about just querying from parent folder of Folder A:

    select count(*) from dm_document where FOLDER('/Cabinets/Folder/Folder/Folder ', descend)
  • @DCTM_Guru Hi, thanks for suggestion.  I can't run that because the Root or Parent folder has other folders.  I included a screenshot that might help with understanding my issue. Thanks again

  • PedroMaiaPedroMaia Senior Consultant (OpenText) EM
    There is no magic. You need to be able to define in DQL the folders you want to count.
    You need one or more patterns:

    where FOLDER('/Cabinets/Folder/Folder/Folder ', descend)
    AND (object_name LIKE <Pattern> OR object_name LIKE <Pattern>... )

    or you need to list all the folders:

    where FOLDER('/Cabinets/Folder/Folder/Folder ', descend)
    AND object_name IN ("Folder1", "Folder 2", ...)

    Pedro Maia
    Senior Consultant

  • edited June 26
    In this scenario, I would just write a bunch of SELECT DQL using Excel and CONCATENATE function.  We do this whenever we need run a bunch of repetitive queries and just substitute appropriate variables (ie folder name): 

    =CONCATENATE("select count(*) from where FOLDER('/Cabinets/Folder/Folder/Folder/",$A$1,"', descend);")
  • @PedroMaia Hi, thanks for the suggestion.  I tested with just a few folders and received a count of zero.  Am i missing something? Thanks

  • Your query is against dm_document WHERE object_name = 'Letter A' or object_name = 'Letter B' or …  This is against the name of the documents, not the parent folder, so of course you count returns 0.  Your original query is fine:

    select count(*) from dm_document where FOLDER('/Cabinets/Folder/Folder/Folder/Folder A ', descend)

    Just replace Folder A with rest of the folders as different select queries.
  • @DCTM_Guru oh gotcha, you're saying i just have to keep repeating the query 26 times (26 folders).
  • PedroMaiaPedroMaia Senior Consultant (OpenText) EM
    edited June 29
    I don't want to make this too complicated, but if all folders you want are 'Letter A' to 'Letter Z', you could try something like:

    SELECT count(DISTINCT d.r_object_id) FROM dm_document d, dm_folder f
    WHERE FOLDER('/Cabinets/Folder/Folder/Folder', descend)
    AND ANY d.i_folder_id = f.r_object_id
    AND f.object_name LIKE 'Letter%'

    On the other hand, if you're going to work with DQL you need to study it first to be sure you understand the basics.

    Pedro Maia
    Senior Consultant

  • @PedroMaia Thanks for the reply, i tried your formula and it turned up with a zero count.  We are moving away from Documentum altogether and migrating into a new system, so learning the DQL language will not help me unfortunately.  Thanks again
Sign In or Register to comment.