DQL query to find folder path of a document from its id.

DQLs are really confusing when it comes to repeating attributes

I have a requirement to fetch folder path of a document from its object_id. For the purpose I wrote the following query:-

SELECT r_folder_path FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='090000c8801d7661')

As a result i get a couple of result rows i.e.

Empty[]

[\ParentFol\AnotherFol]

Definitely \ParentFol\AnotherFol is the correct folder path of the document. First of all i would like to know that why am i getting an emty value as well?

More importantly if I want to filter the empty result set I wrote down the following query:-

SELECT r_folder_path FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='090000c8801d7661') AND r_folder_path <> ''

But when i run this query I keep getting the following error:-

[DM_QUERY_E_REPEATING_USED]error: "You have specified a repeating attribute (r_
folder_path) where it is not allowed."

Is there a workaround to this? Any suggestions to get over the repeating attrbs dilemma ?

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

Francois Dauberlieu

That's because it's a repearing attribute. This one shoud work better

SELECT r_folder_path,r_object_id FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='090000c8801d7661')

Check the repeating attribue handling section of the DQL Reference Manual for more information.

All comments

Francois Dauberlieu

That's because it's a repearing attribute. This one shoud work better

SELECT r_folder_path,r_object_id FROM dm_folder WHERE r_object_id IN (SELECT i_folder_id FROM dm_document WHERE r_object_id='090000c8801d7661')

Check the repeating attribue handling section of the DQL Reference Manual for more information.

Raghu_A

That's great. I guess it worked for me

But i don't really understand the reason behind it. AFAIK DQL does return empty values as well so just by using r_object_id in the Select statement should not really have solved the problem. Isn't it or am i mis understanding some part of it?


SELECT r_folder_path,r_object_id FROM dm_folder WHERE r_object_id IN
(SELECT i_folder_id FROM dm_document WHERE
r_object_id='090000c8801d7661')

Francois Dauberlieu

The underlying reason is the way Documentum handles the tables and their records in the RDBMS.

To be honest, I never really wanted to understand why they did it like that, maybe, with the technology available at the time, that was the best they came up with, maybe for some other reasons. Doesn't matter to me, or should I say, dowsn't matter to me anymore. It doesn't matter to me since the day I had exactly the same problem (at the time, there weren't much forums such as this one out there) and ended up (hating every bit of it, like everybody else) RTFM'ing the DQL ref Manual.