DQL : Important DQL Queries

Important DQL Queries

• Basic DQL Statement
• select object_name, title, r_creation_date from dm_document
• Where clause with
• Strings in single quotes
• select * from dm_document where owner_name = 'jdoe'
• Strings to lower case comparison in where clause
• select count(*) from dm_document where lower(a_content_type) = 'html'
• Numbers - no quotes
• select * from dm_document where owner_permit !=0
• Date
• select * from dm_document where r_creation_date > Date('07/01/2012','MM/DD/YYYY')

• Find blank/ empty folder

SELECT f1.object_name, f1.r_object_id, r_creation_date, f1.r_folder_path, r_modifier, r_creator_name
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 r_creation_date