How to get value assistance DQL statement?

lastnitescurry
edited August 25, 2016 in Documentum #1

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?

Tagged:

Comments

  • dnvhariprasad
    edited May 16, 2012 #2

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

    image
    image
  • ss-chemours
    edited August 23, 2016 #3

    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
    edited August 23, 2016 #4

    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
    edited August 24, 2016 #5

    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
    edited August 24, 2016 #6

    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
    edited August 24, 2016 #7

    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
    edited August 24, 2016 #8
    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
    edited August 25, 2016 #9

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