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://www.dctmcontent.com/Lists/DQL/DispForm.aspx?ID=131&ContentTypeId=0x0100D94FEDC8CA5B554887150EA97B552C91

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?

Find more posts tagged with

Documentum

Comments

dnvhariprasad

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'.

ss-chemours

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.

ss-chemours

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;

DCTM_Guru

Your solution requires the tables/view to be registered tables (hence dm_dbo prefix). Not sure these tables/views are registered by default. Brian's queries do not make that assumption and can be executed in any environment.

ss-chemours

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.

ss-chemours

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.

Haroon_A

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)

ss-chemours

; I thought ROW_BASED only affected how the results were displayed. But, your correct, it works! Learn something new every day. Thanks!