Useful Dql queries

Query To get users and groups of an acl

select distinct grl.users_names, gr2.i_supergroups_names
from dm_group_r grl, dm_group_r gr2
where grl.r_object_id = gr2.r_object_id and gr2.i_supergroups_names IN (SELECT r_accessor_name from dm_acl where object_name = 'ge_og_tm_rlst_int_ref_acl')
**************************************************************
Query to fetch users in an acl

select user_name from dm_user where user_name in (SELECT r_accessor_name from dm_acl where object_name like 'ge_og_pm%')

Query to fetch groups in an acl

select group_name from dm_group where group_name in (SELECT r_accessor_name from dm_acl where object_name like 'ge_og_pm%')


***************************************************************
To select folders and their permission details within a given folder:

SELECT DISTINCT s.object_name, ar.r_accessor_name, ar.r_accessor_permit, ar.r_accessor_xpermit, sr.r_folder_path
FROM dm_acl_s a, dm_acl_r ar, dm_folder s, dm_folder_r sr
WHERE a.r_object_id = ar.r_object_id
AND s.r_object_id=sr.r_object_id
AND a.object_name = s.acl_name
AND sr.r_folder_path like '/XXX%'
AND sr.i_position = -1


***************************************************************
Query to fetch latest update from set of records

select object_name,owner_name, r_creation_date,r_modify_date,r_modifier from dm_sysobject where FOLDER('/Oil & Gas Comm Ops (Finance & Legal)/Sales/Pre-qualification/GE O&G
Reference Lists/Internal use only/Confidential Agreements (ordered by legal entity)',descend) order by r_modify_date desc ENABLE(RETURN_TOP 1)
*************************************************************************
Query to fetch first record from a set of records

select * from dm_sysobject ENABLE(RETURN_TOP 1) which
will give you the first row..
*************************************************************************
Query to fetch group names of a user

Select group_name from dm_group where any i_all_users_names='Matucci, Alberto'

**************************************************************************
Query to get subfolders of a folder

Select * from dm_folder WHERE Folder('/Oil & Gas Comm Ops (Finance & Legal)/Sales/Commercial Documentum/Sites/Massa/Test1/Link To PM Docm/Test1012', descend);

**************************************************************************
QUERY TO CREATE OBJECT TYPE

CREATE TYPE large_string_test (large_string CHAR(255)) WITH SUPERTYPE dm_document
For altering the attributes of an object

ALTER TYPE large_string_test MODIFY large_string CHAR(2000)

Examples :

CREATE TYPE "mycustomtype" (firstname string(64),
country string(64) ,
age int,
height double,
phone string(10) REPEATING)
WITH SUPERTYPE "dm_document" PUBLISH

Modify a Simple Object Type (Adding new Attributes)
ALTER TYPE "mycustomtype"
Add lastname string (64),
height double,
weight double
PUBLISH

Modify a Simple Object type (Modify existing attribute)
ALTER TYPE mycustomtype
MODIFY firstname string (121)

To Drop object type:

DELETE type_name (ALL) OBJECTS
after that
DROP TYPE type_name

**************************************************************************

To give top ten results of a query

SELECT * FROM dm_sysobject ENABLE (RETURN_TOP 10)

*************************************************************************
Query to check particular user is present in a particular acl

select object_name from dm_acl where any r_accessor_name='Bechi, Alessandro' and object_name='ge_og_pm_acl_105711011'

*************************************************************************
Query to get the parent folder of folder

select r_folder_path, object_name from dm_folder where r_object_id in (select All i_folder_id from dm_folder where r_object_id = '0b00138a80355b55')
*************************************************************************
Query to get the object name and folder path of ge_og_pm_project object type which is modified after a given date.

select r_object_id,object_name,r_folder_path from ge_og_pm_project where r_modify_date>'31/jan/2011'
**************************************************************************
Query to fetch the object types under a particular folder

select distinct r_object_type from dm_folder where folder('/Oil & Gas Comm Ops (Finance & Legal)/Sales',descend) and r_object_type not in ('ge_og_pm_project','ge_og_pm_subfolder','dm_folder')
**************************************************************************
Query to update a object

update ge_og_pm_project object set ge_mat_plannibg_mgr='' where object_name='Test Project'
**************************************************************************
DQL to find all the folders in a Cabinet, where the folders are contentless

SELECT f1.object_name, f1.r_object_id, f1.r_folder_path
FROM dm_folder f1
WHERE FOLDER(’/Cabinetname’,descend)
AND NOT EXISTS (SELECT f2.object_name FROM dm_sysobject f2 WHERE ANY f2.i_folder_id = f1.r_object_id)
ORDER BY object_name
*************************************************************************
Query to find out objects with lock or checked out

Select r_object_id, r_lock_owner from ge_og_pm_document where r_lock_owner is not
nullstring

**************************************************************************
Query to find path of the checked out document

select r_object_id,r_folder_path from dm_folder where r_object_id in (select i_folder_id from dm_document where r_object_id in (Select r_object_id from ge_og_pm_document where r_lock_owner is not
nullstring)) order by r_folder_path
**************************************************************************
Query to add user to many groups

UPDATE dm_group OBJECTS APPEND users_names = 'SR, Karthik' WHERE group_name IN ('fe_general', 'enterprise');
***************************************************************************
DQL on Dates

To Get The Current Day and time

select DATETOSTRING(DATE(NOW),'dd/mm/yyyy') as systime from dm_server_config

To Get Todays Date

select DATETOSTRING(DATE(TODAY),'dd/mm/yyyy') as systime from dm_server_config