Grouping Repeating Attributes

One of the useful features of DQL is its ability to group repeating attributes for a single object into a single row of a result set. For example the following query on my system just returns the repeating authors attribute:

This time there are only 3 “rows”, one for each of the objects found in the query, and each repeating attribute for an object is returned with all the other repeating attributes for that object.

Notice that I added in an “order by r_object_id”. This is essential to ensure that the grouping works as expected. This is a point that is not particularly clear in the documentation and it is somewhat limiting, as it prevents the user from ordering the objects in some other useful way like by object_name (or UPPER(object_name) as so beloved of most of Documentum’s user interfaces these days).

The implementation of repeating attribute grouping assumes that the rows will be returned from the database in r_object_id order. However it is easy to forget this and not even notice in development or testing, as many times the database will naturally return rows in r_object_id order. However with a little effort it is possible to produce a test case like the following:

All I have done is remove the “order by” clause from the query. In this particular case we now have 6 rows with each object reported twice! I leave details of what is going on to the next post but I’ll leave you with a little puzzle. I can “fix” this particular query by applying some hints:

Note I’m not suggesting this as a reliable way to solve the problem (in general there isn’t one short of having r_object_id as the first column in the order by) but it gives a clue as to how Documentum implements this feature.