Object related DQL queries

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


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.



Date:11 November 2022

Very helpful document. Excellent job. Please share more.

Leave a Reply

Your email address will not be published. Required fields are marked *