Needful things Part B

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>)

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.

Needful things Part A

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
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
i.r_object_id = d.r_object_id 
AND i_latest_flag = 1 
AND DATEFLOOR(day, due_date)<=DATEFLOOR(day, DATE(NOW)) 
ORDER BY due_date,r_object_id