Useful DQLs

1). Changing the folder location of a document [New folder object id=0b024afe804641qq, Old folder object  id =09024afe7044b7bd]
update dm_document object set i_folder_id='0b024afe804641qq' where r_object_id='09024afe7044b7bd'

2). Changing the folder location of all documents present in a specific folder[new folder object id =0b024afe80464199]
update dm_document object set i_folder_id='0b024afe80464199' where folder('/Workgroups/OldFolderLocation',descend)

3). Getting count of documents present in a particular folder
select count(*) From dm_document(all) where folder('/Workgroups/NewFolderLocation',descend)

4). Getting all or a specific property info of documents present in a folder
select r_object_id From dm_document(all) where folder('/Workgroups/MyFolder',descend)
select * From dm_document(all) where folder('/Workgroups/MyFolder',descend)

5).Getting folder path for documents (when object ids are known)
SELECT r_folder_path FROM dm_folder WHERE r_object_id in(select i_folder_id From dm_document(all) where r_object_id in ('0b024afe804641sss','0b024afe804641wer','0b024afe804641010'))

6). Getting document of a particular type
SELECT * from dm_document where a_content_type='html'

7). Getting a list of users belong to a group
select * from dm_user where user_group_name='group_name_to_search'
SELECT user_name,client_capability FROM dm_user WHERE user_name IN (SELECT users_names FROM dm_group WHERE group_name like 'group_to_search%') ORDER BY user_name

8). Changing the owner/target group of the documents
update dm_document objects set owner_name='new_ownername',set  targetgroup='new_group' where folder('/Workgroups/MyDocFolder',descend)

9). Get the document list that were modified after a particular date and are present in a specific folder

select count(*) From dm_document where folder('/Workgroups/MyDocFolder',descend) AND r_modify_date > DATE('01/01/2010 09:00:00','MM/DD/YYYY hh:mm:ss')

select * From dm_document where folder('/Workgroups/MyDocFolder',descend) AND r_modify_date > DATE('01/01/2010 09:00:00','MM/DD/YYYY hh:mm:ss')

10). Get mandatory attribute information of a Custom Type
select * from dmi_dd_attr_info where type_name like 'customType01'  and is_required = 1


11). Get all attribute information of a Custom Type

select * from dmi_dd_attr_info where type_name like 'customType01'

12). Query - get documents which are being worked on by users (checked out)
select * from TYPE where r_lock_owner is not NULLSTRING and r_lock_date is not NULLDATE

13). Query - get document which are not immutable (enables meta-data update)
update TYPE(all) object set Field1='NewValue' where Field1='OldValue' and r_immutable_flag=False 

14). Get all users which belong to a specific group

select group_name,i_all_users_names from dm_group where group_name like '%group1%' or group_name like '%group2%' order by group_name