(private) sorry...!! contact@ecmnotes.com


List all documents owned by a specific user:

SELECT object_name FROM dm_document WHERE owner_name = ''

List all documents that contain specific keywords:

SELECT object_name FROM dm_document WHERE ANY keywords = '' and ANY keywords = ''

 List all documents that have been checked out since yesterday, and who checked them out:

SELECT object_name, r_lock_owner FROM dm_document WHERE r_lock_date >= DATE(YESTERDAY)

List all documents created by a specific user within a specific time frame:

SELECT r_object_id, object_name, r_object_type,a_content_type, r_link_cnt FROM dm_document WHERE r_creation_date >= DATE('') and r_creation_date < DATE('') and r_creator_name = ''

Return the folder location for a particular document:

SELECT r_object_id, r_folder_path FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE lower(object_name) LIKE '%specification%’and owner_name = USER)

 Return all dm_document objects where the current user has at least VERSION permission:

SELECT FOR VERSION object_name, keywords, subject FROM dm_document

Return all versions, not just CURRENT versions, of selected documents:

SELECT r_object_id, object_name, r_version_label,i_chronicle_id FROM dm_document (ALL) WHERE object_name = ’'

 Return all documents where a repository user is listed as an author:

SELECT r_object_id FROM dm_document WHERE ANY authors IN (SELECT user_name FROM dm_user)

Return the no of versioned documents for a particular type:

SELECT count(*), i_chronicle_id, object_name from  (all) group by i_chronicle_id, object_name

Return current version of documents modified from in a certain period of time:

SELECT * from type where r_modify_date>Date('') and r_modify_date

Return the list of documents modified by particular user from the group:

SELECT r_object_id from type(ALL) where r_modifier in (select i_all_users_names from dm_group where group_name = 'group_name') or r_lock_owner in(select i_all_users_names from dm_group where group_name = 'group_name') or owner_name in(select i_all_users_names from dm_group where group_name = 'group_name') or r_creator_name in (select i_all_users_names from dm_group where group_name = 'group_name')

Return the list of documents that have more than 1 value in the repeating attribute:

SELECT object_name,count(distinct ) from type group by object_name having count(*) >1

Unlock a document:

UPDATE type OBJECT SET r_lock_owner=’’ WHERE r_object_id='OBJECTID'

Return the list of folders inside a cabinet which have a particular type of documents:

SELECT distinct i_folder_id FROM type WHERE folder('/A',descend) and r_object_type = 'type' and folder('/A',descend)

Return the folder path of all the versions of a document:

SELECT d.r_object_id, d.r_version_label, f.r_folder_path from  dm_document (all) d, dm_folder f where any d.i_folder_id = f.r_object_id and folder ('/A’, descend) and f.r_folder_path is not nullstring

Return the files inside user's home cabinet:

SELECT count(*),owner_name from dm_document (all) where acl_name like 'ACL_Home%' and owner_name in(select user_name from dm_user where user_login_name like '1%') group by owner_name

Leave a Reply