These examples are meant to give you a head-start on the basic DQL syntax.
Create a Object Type
This script creates an object type edoccustomtype with supertype as dm_document
CREATE TYPE “edoccustomtype” (firstname string(64), country string(64) , age int, height double, phone string(10) REPEATING) WITH SUPERTYPE “dm_document” PUBLISH
Modify a Object Type: Add new Attributes
ALTER TYPE “edoccustomtype” Add lastname string (60), height double, weight double PUBLISH
Modify a Object type: Modify existing attribute
There are many constraints on modifying an existing attribute Refer to DQL Manual for more information
ALTER TYPE edoccustomtype MODIFY firstname String (200)
Modify a Simple Object Type: Delete an Attribute
ALTER TYPE edoccustomtype drop weight
Dropping an Object type
DROP Type “edoc_custom_object”
Delete all Objects of an Object type
Delete all objects
DELETE edoc_custom_object objects
Delete all versions of of all objects
DELETE edoc_custom_object (all) objects
Find all attributes of an Object Type
DESCRIBE edoccustomtype
Create a new Object and set its attributes
CREATE edoccustomtype OBJECT SET firstname = ‘First Name’, SET country= ‘US’, SET age=30 SET phone [0]= ‘12345678’
phone is a repeated attribute, age is a integer and firstname and country are strings
Update an existing Object’s Attribute
UPDATE dm_document object SET object_name = ‘new_name’ WHERE object_name = ‘old_name’
Add a value to an existing object’s repeated attribute
UPDATE dm_document objects SET keywords[0]=’value’ WHERE r_object_id=’09xxxxxx’
Delete an Object from repository
DELETE dm_document object WHERE object_name=’object_name’
Select Single value attributes of an Object with single attribute in where clause
SELECT r_object_id, object_name FROM dm_document WHERE object_name=’test’
Select a Repeated Attributes of an Object with repeated attribute in where clause
SELECT r_version_label FROM dm_document WHERE any r_version_label in (‘CURRENT’)
Find all versions of an object
SELECT r_object_id, r_version_label FROM dm_document (ALL) WHERE object_name=’MyDocument’
Usage of Dates in Where Clause
SELECT r_object_id, object_name FROM dm_document WHERE r_modify_date > DATE(’01/01/2008 00:00:00′,’MM/DD/YYYY hh:mm:ss’) AND r_modify_date < DATE(‘TODAY’)<br>(This will return object id and name of all the objects that was created between today and 01-01-2008)
Usage of DATEDIFF Function
DATEDIFF(date_part, date1, date2) – DATEDIFF function returns the number that represents the difference between given two dates. Valid values for date_part are a year, month, week, and day and date1 is subtracted from date2.
SELECT * FROM dm_document WHERE DATEDIFF(week, “r_creation_date” , DATE(TODAY))<=2<br>(This query will return all dm_documents that are created between today and the last 2 weeks)
Usage of As in the Query
SELECT object_name as name FROM dm_document WHERE r_object_id=’09xxxxxx’
Usage of Distinct
This returns all the unique object names of type dm_document.
SELECT DISTINCT object_name FROM dm_document
Usage of NULLSTRING
This Query will return Object name of all the objects that have my_attributes as blank
SELECT object_name FROM my_object_type WHERE my_attribute is NULLSTRING
This Query will return Object name of all the objects that have some values in my_attributes (non-blanks)
SELECT object_name FROM my_object_type WHERE my_attribute is not NULLSTRING
Query to search a document (with full-text indexing )
SELECT * FROM dm_document SEARCH document contains ‘test’
Find all groups a user belongs to
SELECT group_name FROM dm_group WHERE any users_names in (‘user_name’)
Find Folder Path of an Object
SELECT r_folder_path FROM dm_folder WHERE r_object_id in (SELECT i_folder_id FROM dm_document WHERE object_name=’object_name’)Find Objects those have Same (Duplicate) Name
SELECT object_name, count(*) FROM dm_document GROUP BY object_name HAVING count (*) > 1 ORDER BY object_name
Find all locked object that was locked one day before
SELECT r_object_id, r_lock_date FROM dm_document WHERE r_lock_owner is not NULLSTRING and r_lock_date is NOT NULLDATE and r_lock_machine is not NULLSTRING and DATEDIFF(day, r_lock_date, DATE(TODAY))<=1 ORDER BY r_lock_date
To Return only specified number of Records
SELECT * FROM dm_sysobject ENABLE (RETURN_TOP 10)<br>This RETURN_TOP hint returns the number of records specified.
This query will return only 10 records.
Comments:
Name:Steve
Date:11 November 2022
Very helpful document. Excellent job. Please share more.