DQL for only returning latest versions of documents that are in release or draft status

Fernando C

Hello, I am fairly new to Documentum and DQLs. What would be the query for only fetching the latest version of documents that are in Release or Draft status, not necessarily the 'CURRENT' released version. This is the DQL I have so far:

select object_name, r_object_id, authors, title, subject, r_version_label, a_status, acl_name, acl_domain, r_modify_date, r_object_type, owner_name from dm_document where ( lower (a_status)='released' or lower(a_status)='draft' ) and ( owner_name ='<Author Name>' or any authors='<Author Name>' ) order by object_name asc

My only issue is that this DQL is returning the 'current' version documents that are in release or draft status. So, if a document has a later version that is not released or in draft, the query is returning the 'current' released version. However, I would like the query to only return the latest version of documents that are in released or draft status. If the document's latest version is not in release or draft status, I do not want any versions of the document returned in the query at all. Is that possible?

Any help is greatly appreciated! 😀

Hicham Bahi

By default DQL will query only CURRENT versions. So you should add the ALL keyword to your query:

from dm_document (all)

If you only want the latest version in a particular state, then it becomes more complicated. Alternatively, you could sort by r_object_id in descending order.