DQL Query to count specific folders

dj_
dj_ Systems Administrator
Hi,

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,

Dj
Tagged:

Comments

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

    select count(*) from dm_document where FOLDER('/Cabinets/Folder/Folder/Folder ', descend)
  • dj_
    dj_ Systems Administrator
    @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


  • PedroMaia
    PedroMaia 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
    OpenText

  • DCTM_Guru
    edited June 26, 2020 #5
    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);")
  • dj_
    dj_ Systems Administrator
    @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.
  • dj_
    dj_ Systems Administrator
    @DCTM_Guru oh gotcha, you're saying i just have to keep repeating the query 26 times (26 folders).
  • PedroMaia
    PedroMaia Senior Consultant (OpenText) EM
    edited June 29, 2020 #9
    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
    OpenText

  • dj_
    dj_ Systems Administrator
    @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.