Documentum Query Language issue

Is there any SQL listagg function equivalent in DQL?
I wanted to combine my 2 records with same r_object_id in one record.
For Example. one record has r_version_label as "CURRENT"
and other record has r_version_label as "1.3" and both records have same r_object_id.

If not listagg function, is there any other way to combine both the records as one record using DQL.

Comments

  • r_version_label is repeating value field. This is by design, since what is "CURRENT" is not dot directly tied to version number. Combining the value doesn't make sense and will cause corruption issues, since Documentum internal queries look for CURRENT version label for current version of the record.

  • Thanks for your response DCTM_Guru :smile: !!!

    I wanted to view records with same r_object_id in one single line using DQL query on my view page.

  • Your nomenclature is wrong. Each document/object/record can have single value attributes (e.g. object_name) or repeating value attributes (e.g. r_version_label). If you want repeating values returned in the same row, just pass r_object_id at the beginning of DQL:

    select r_object_id, r_version_label from dm_document where ...

  • As you said I have passed r_object_id at the beginning only of my DQL query.
    I also tried with the nomenclature select r_object_id, object_name, r_version_label from table_name,
    but still getting two different records for same r_object_id.

  • You are NOT getting two different records, you are getting concatenation of repeating value field for singe record. Just query for single object (hopefully this will help you understand better):

    select r_object_id, r_version_label, object_name where r_object_id = '...'

  • Below is our DQL query which is giving 2 different rows for same r_object_id:

    would appreciate your help on this query :smile:

    select a.r_object_id,a.object_name,a.r_version_label,b.rcu_status,a.r_creation_date,a.applicant_type,a.document_category,a.OWNER_NAME,a.r_modifier,a.applicant_name as version_label,a.a_content_type from test_document(all) a LEFT OUTER JOIN (SELECT * FROM dm_dbo.custom_table) b ON (a.r_object_id = b.r_object_id) where folder('/TEST/Testing/123456789',DESCEND) and a.r_object_type = 'test_document' order by a.document_category,a.applicant_type,a.object_name,a.r_creation_date ENABLE (ROW_BASED)

  • This is b/c you are using JOIN. This has nothing to do with different records. I'm not a dba, so I cant tell you to rewrite this.