*** get CURRENT TIME on server ***
select DATE(NOW) as systime from dm_server_config
————————————————————————————-
*** get the implicit version label ***
select s.r_object_id,s.object_name,r.r_version_label from dm_sysobject s, dm_sysobject_r r where r.r_object_id = s.r_object_id and
r.i_position = -1
————————————————————————————-
*** include r_object_id to remove blank rows
select object_name, r_object_id, a_expiration_date from dfas_common where folder(’/Raj Srinivasan’) and any a_expiration_date is not nulldate
————————————————————————————-
*** select documents with pdf renditions (optimized for performance) ***
select * from dm_document where exists(select * from dmr_content where any parent_id=dm_document.r_object_id and full_format=’pdf’)
————————————————————————————-
*** select all documents that have nulldate in repeating attribute
select object_name from dfas_common where folder(’/Raj Srinivasan) and r_object_id NOT IN (select r_object_id from dfas_common where folder(’/Raj Srinivasan’) and any a_expiration_date is not nulldate)
————————————————————————————-
*** select all documents that have nulldate in repeating attribute or a_expiration_date has past
select count(*) from dfas_common where folder(’/content/Corporate Resources/Human Resources’,descend) and (any a_expiration_date <
date(today) or (r_object_id NOT IN (select r_object_id from dfas_common where folder(’/content/Corporate Resources/Human Resources’,descend)
and any a_expiration_date is not nulldate)))
————————————————————————————-
*** select folderpath with filename
select distinct s.object_name, fr.r_folder_path from dm_sysobject (all)
s,dm_sysobject_r sr,dm_folder_r fr where sr.i_position = -1 and
sr.r_object_id = s.r_object_id and fr.r_object_id = sr.i_folder_id and
fr.i_position = -1 and fr.r_folder_path like ‘/ChemNet/%’
order by fr.r_folder_path,s.object_name
————————————————————————————-
*** empty folders ***
SELECT r_object_id,r_folder_path FROM dm_folder f WHERE r_object_id NOT IN
(SELECT distinct i_folder_id FROM dm_sysobject WHERE any i_folder_id = f.r_object_id and folder(’/content’,descend)) and folder(’/content’,descend)
————————————————————————————-
*** NULL a_effective_date ***
select object_name from dfas_common where folder(’/images’, descend) and r_object_id NOT IN (select r_object_id from dfas_common where folder(’/images’, descend) and any a_effective_date is not nulldate)
————————————————————————————-
*** select component from workitem ***
select
r_component_id
from
dmi_package p
where
exists (select r_object_id
from dmi_workitem w
where
w.r_object_id = and
w.r_workflow_id = p.r_workflow_id and
w.r_act_seq_no = p.r_act_seq_no)
————————————————————————————-
*** select renditions ***
SELECT s.object_name, f.dos_extension
FROM dm_dbo.dm_sysobject_s s, dm_dbo.dmr_content_r c, dm_dbo.dm_format_s f
WHERE (c.parent_id = s.r_object_id)
and (c.i_format = f.r_object_id)
AND c.page=0 AND f.dos_extension <> ‘xml’
AND folder(’/whatever’)
————————————————————————————-
*** find workflow given document ***
select * from dm_workflow where r_object_id in (select r_workflow_id from dmi_package where any r_component_id in (select r_object_id from
dm_sysobject (all) where i_chronicle_id in (select i_chronicle_id from dm_sysobject where r_object_id=”))) and r_runtime_state=’1′
————————————————————————————-
*** return only inherited attributes ***
SELECT attr_name FROM dmi_dd_attr_info WHERE type_name = ‘my_custom_type’ AND attr_name NOT IN
(SELECT attr_name FROM dmi_dd_attr_info WHERE type_name = ‘dm_document’)
————————————————————————————-
Documents that are uploaded in to docbase b/w Aug 2004 and Oct 2004
select a.r_object_id,a.r_creation_date,a.r_modify_date,b.user_group_name
from dm_document a, dm_user b
where (a.r_creation_date >= Date(’08/01/2004′,’mm/dd/yyyy’) and a.r_creation_date <= Date(’10/31/2004′,’mm/dd/yyyy’) ) and a.r_modifier = b.user_name
————————————————————————————-
Documents and it’s size
select a.object_name, b.content_size/1024 as “TotalSize_KB”
from dm_sysobject a, dmr_content b
where
any b.parent_id = a.r_object_id
and FOLDER(’/ChemNet/Home’,descend)
and full_format=’aspx’
and rendition > 0
————————————————————————————-
Subject: Why do we create four views for each subtype?
Note: For example the type dm_document has four views:
dm_document_sp
dm_document_sv
dm_document_rp
dm_document_rv
The two _sp and _rp views are used by DQL and the two
_sv and _rv views are used by the Object Manager.
Also, the _sp and _sv views are for single (non-repeating) attributes, while the _rv and _rp views are for repeating attibutes.
————————————————————————————-
Label Text of the either Sytem Type or Custom Type
select label_text from dm_nls_dd_info where parent_id in
(select r_object_id from dm_aggr_domain where type_name = ‘field_type’)
Results:
Content Location
Keywords Category
Business Unit Owner
————————————————————————————-
DQL to list all documents attributes and their associated folder path
select s.object_name, fr.r_folder_path
from dm_document s, dm_sysobject_r sr,
dm_folder_r fr
where s.i_is_deleted = 0
and sr.i_position = -1
and sr.r_object_id = s.r_object_id
and fr.r_object_id = sr.i_folder_id
and FOLDER(’/ChemNet’, descend)
and fr.r_folder_path like ‘/ChemNet/%’
order by fr.r_folder_path,s.object_name
————————————————————————————-
*** Web cabinet of a content ***
SELECT object_name FROM dm_cabinet
WHERE r_object_id IN (SELECT i_cabinet_id FROM dm_folder
WHERE r_object_id IN (SELECT i_folder_id FROM dm_document (ALL)
WHERE object_name like ‘test%’)) AND r_object_type=’wcm_channel’
OR
select r_object_id, object_name from wcm_channel where r_object_id in
(select i_ancestor_id from dm_folder where r_object_id in
(select i_folder_id from dm_sysobject where object_name = ‘test’))
————————————————————————————-
*** To get the first level folders from the cabinets ***
select object_name from dm_folder where any i_folder_id in (select r_object_id from dm_cabinet where object_name=’Cabinets’)
————————————————————————————-
*** To show the previous Active content ***
select r_object_id,i_chronicle_id,DATETOSTRING(DATE(TODAY),’ddmmyyyy’) as sysdate from dm_document (ALL) where object_name like ‘test%’ and
FOLDER(’/ChemNet/ChemonicsProcess/Bidding’) and
any r_version_label = ‘Active’
————————————————————————————-
To find the folders for particular group for specified permission
select for write object_name from dm_folder where acl_name IN (select object_name from dm_acl where any r_accessor_name = ‘dm_world’) and folder(’/ChemNet’,descend)
or
select object_name from dm_folder where acl_name IN (select object_name from dm_acl where any r_accessor_name = ‘dm_world’ and any r_accessor_permit = ‘7′) and folder(’/ChemNet’,descend)
1 – NONE
2 – BROWSE
3 – READ
4 – RELATE
5 – VERSION
6 – WRITE
7 – DELTE
————————————————————————————-
DFC to insert rows into registered table
public static IDfCollection execQuery(String queryString, IDfSession session)
throws DfException{
IDfCollection col = null; //Collection for the result
IDfClientX clientx = new DfClientX();
IDfQuery q = clientx.getQuery(); //Create query object
q.setDQL(queryString); //Give it the query
// example queryString: “insert into dm_dbo.your_registered_table
(field1, [field]) values(,[
col = q.execute(session, IDfQuery.DF_EXEC_QUERY);
System.out.println(”Query executed.”);
return col;
}
————————————————————————————-
How to avoid redundancy for this simple query?
select distinct d.object_name,sys.authors from dm_document d, dm_sysobject_r sys
where folder (’/xyz/xyz_first’) and
d.r_object_id = sys.r_object_id and
(sys.i_position=-1 or (sys.i_position<=-2 and sys.authors is not null))
This article is from http://dctm-knowledgebase.blogspot.com