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