Tag Archiv: DQL

Helpful dummy objects

Example: You want to update many objects with the same attributes, but some of your result sets should not be updated. Or you must update a selection of objects with different attributes.

One of the possible solutions is to create many unique update statements or WHERE conditions. Or you create a big IN or NOT IN condition with many object_ids.
The best practice is to create a dummy dm_document object in your repository (i.e. with the DQL Tester). The included or excluded objects_ids are stored in the keywords of the dummy dm_document object. The dm_document object can store a huge number of keywords (> 10 000). In practice I have saved more than 30k object_ids in one document. The SELECT or UPDATE statement is very easy and is issued like this:

SELETCT r_object_id, r_lock_owner, object_name, r_lock_date 
 FROM r_and_d_document (ALL) 
 WHERE r_object_id 
 NOT IN (select keywords from dm_document WHERE object_name=<dummy name>)
 ORDER BY 4 DESC

This statement selects all checked out documents except a number of predefined documents. You can execute many select statements to select the excluded documents. This has the advantage that all of your results are documented.

Some helpful tiny DQL queries:

Select all Documents which are currently running in a Workflow

select distinct
d.object_name as document_name,
d.r_object_id as doc_identifier,
f.object_name as wfl_name,
f.r_act_name as wf_type,
f.supervisor_name as started_by,
f.r_object_id as wf_id 

from dmi_package p, dm_workflow f, dm_document (all) d
where r_workflow_id in (select r_object_id from dm_workflow)
AND p.r_workflow_id = f.r_object_id
AND p.r_component_id = d.r_object_id
ORDER BY 5 ASC
enable (row_based)

Documents attached to workflow tasks in a user’s Inbox that are overdue

SELECT DISTINCT r_object_id,due_date 
FROM dm_dbo.inbox_docs i, dm_document (ALL) d
WHERE
i.r_object_id = d.r_object_id 
AND i_latest_flag = 1 
AND DATEFLOOR(day, due_date)<=DATEFLOOR(day, DATE(NOW)) 
AND due_date IS NOT NULLDATE 
ORDER BY due_date,r_object_id