How to get value assistance DQL statement?
Mostly this is a reminder for myself whenever I need to look at this again. I have wondered about this for a while, seeing as I hate duplicating configurations. There is a question at end if someone would like to do a bit of work for me please
Assuming I have configured the following DQL as the value assistance for an attribute.
SELECT group_name, description FROM dm_group WHERE group_name IN (SELECT groups_names FROM dm_group WHERE group_name = 'ucl_rs_fis_share_all_depts') ORDER BY 2
How can I get this DQL in code? Is there a simple query for me to use, which would be ideal?
By the power of a internet search engine and I found the following but this does not work on my 6.6. Developer Edition
select query_attribute, query_string from dm_value_query where r_object_id = (select default_id from dm_cond_id_expr where r_object_id in (select cond_value_assist from dmi_dd_attr_info where type_name='<objtype>' and attr_name='<attribute>'))
http://dctm-knowledgebase.blogspot.co.uk/2009/09/how-to-findget-list-of-values-of.html
https://community.emc.com/message/560204
http://sourceforge.net/projects/dctm/
But the following does work, which I figured out from Object Reference
SELECT DISTINCT query_attribute, query_string FROM dm_value_query WHERE parent_id = ( SELECT r_object_id FROM dm_aggr_domain WHERE type_name ='xyz_folder' AND ANY attr_name ='abc_brian')
and got some validation for, from this conversation
https://community.emc.com/message/240765
but can not get access to this link
http://softwaresupport.emc.com/support/resolutions/dmfrmresolutionview.asp?id=16660
I guess if I had access to that resolution then I would not be wondering why the 2nd DQL works.
Still guessing, I assume the first DQL is valid pre version 6x. Anyone feel like checking for me?
Comments
-
FYI: Following is the content from the support note
RESOLUTION 1 (Using IDQL and IAPI together):
1) Execute the following query:
select * from dm_value_assist where parent_id in(select r_object_id from DM_AGGR_DOMAIN where type_name='<mycustom type>');
Example:select * from dm_value_assist where parent_id in(select r_object_id from DM_AGGR_DOMAIN where type_name='paul213157');
Where 'paul213157' is my custom object type.
Some of the results in this query, will include the r_object_id of the value assistance clauses for that specific type.
2) You can then use IAPI on the results ,dump the r_object_ids listed, such as:r_object_id
5b01824a8000a500
5c01824a8000b104
5c01824a8000a900
An object id of <5b...> represents a fixed list value assistance clause on the
dm_value_list table.An object id of <5c...> represents a query based list value assistance clause on the
dm_value_query table.
3) dump,c,<r_object_id>3a) If your dumping the <5b...> object id:
check the following attribute:
valid_values attribute.
3b) If your dumping the <5c...> object id:
check the following attribute:
query_string attribute.
RESOLUTION 2 (using IAPI only):
1) Dump the custom type and the custom attribute together.
dump,c,t<custom typename>.<attribute>Example:
dump,c,tpaul213157.noway
where paul213157 is my custom type and 'noway' is my fixed list value assistance attribute.
2) Find the cond_value_assist attribute and perform a dump API.
Example:dump,c,<57...>
3) Find the attribute 'default_id'. Dump this attribute.Example:
dump,c,<5b...>
4) Find the attribute, valid_values, which is a repeating valued attribute.
This will contain the fixed list value assistance values used by the custom attribute 'noway'.
0 -
This is an old one, but had a need. I think I found a single DQL statement that works in 6.7 SP1:
select query_attribute, query_string from dm_value_query where r_object_id in (select default_id from dm_cond_id_expr where r_object_id in (select cond_value_assist from dm_dd_info where r_object_id in (select dd_info from dm_domain where r_object_id in (select attr_domain_id from dm_aggr_domain where type_name = 'dm_document'))))Change the last type_name to your desired object type.
0 -
Even better (must be superuser; change 'dm_document' to your desired type name):
select ads.type_name, adr.attr_name, vqs.query_attribute, vqr.query_string from dm_dbo.dm_value_query_sv vqs, dm_dbo.dm_value_query_rv vqr, dm_dbo.dm_cond_id_expr_sv cid, dm_dbo.dm_dd_info_sv ddi, dm_dbo.dm_domain_sv dom, dm_dbo.dm_aggr_domain_sv ads, dm_dbo.dm_aggr_domain_rv adr where vqs.r_object_id = vqr.r_object_id and vqs.r_object_id = cid.default_id and cid.r_object_id = ddi.cond_value_assist and ddi.r_object_id = dom.dd_info and dom.r_object_id = adr.attr_domain_id and ads.type_name = 'dm_document' and ads.r_object_id = adr.r_object_id order by ads.type_name, adr.attr_name;0 -
No, I don't think that's true, IF you are a superuser (as I mentioned) you can query any table without pre-registering. I'm assuming anyone that needs to know Value Assistance DQL is also a superuser, but I suppose that's not necessarily always true. I did not register any of those tables, and I doubt they are registered by default, and it worked. BTW, the reason I am querying directly against the database (by specifying table owner via alias) is to avoid the limitation of dealing with repeating properties in DQL; it doesn't allow the use of repeating properties in a predicate or joins. I am also using the join to show the type name and property name (not available in dm_value_query) for convenience; query_attribute is not always the same as attr_name.
Brian's first query only worked for one attribute; this query provides a listing of all properties with value assistance queries for a type.
Brian's second query did not work as desired for me: it returned multiple rows for the same property. Apparently, historical information is kept in the tables, and I had been modifying VA configuration, so I could not tell from results which one was currently active.
0 -
If you take out "and ads.type_name = 'dm_document' " from where clause, you can get a listing of all VA DQL for all types.
0 -
BTW, the reason I am querying directly against the database (by specifying table owner via alias) is to avoid the limitation of dealing with repeating properties in DQL; it doesn't allow the use of repeating properties in a predicate or joins.
Have you tried using ENABLE(ROW_BASED) predicate, in your query, without querying directly the tables?
In most cases, this will allow you to select repeating field, while joining tables.
Something like this:
SELECT
ads.type_name,
ads.attr_name,
vqs.query_attribute
FROM
dm_value_query vqs,
dm_cond_id_expr cid,
dm_dd_info ddi,
dm_domain dom,
dm_aggr_domain ads
WHERE
vqs.r_object_id = cid.default_id AND
cid.r_object_id = ddi.cond_value_assist AND
ddi.r_object_id = dom.dd_info AND
dom.r_object_id = ads.attr_domain_id AND
ads.type_name = 'dm_document'
ORDER BY
ads.type_name, ads.attr_name
ENABLE(ROW_BASED)0 -
Categories
- All Categories
- 111 Developer Announcements
- 49 Articles
- 102 General Questions
- 123 IM Services
- 40 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 898 Cloud Fax and Notifications
- 77 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 124 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management
- 1 XM Fax