Few days ago my friend asked me a dql query. He wanted to have a list of all the documents and their folder paths in the repository.
It didn’t appear to be a tough one. I knew that the i_folder_id of dm_document is mapped with the r_object_id of the dm_folder to which the document is linked to. A document can be linked to multiple folders and that explains why this i_folder_id is a repeating attribute. The dm_folder has another repeating attribute r_folder_path which stores the folder path of that particular folder. The reason for r_folder_path being repeating is same as that for i_folder_id in case of dm_document.
I told him this dql to get the folder path of all the documents.
SELECT r_folder_path
FROM dm_folder
WHERE r_object_id in
(SELECT distinct i_folder_id
FROM dm_document)
He didn’t look happy enough. What he actually wanted was a list of documents with the folder path. I told him that it is not a problem; we can put a join and get the object_name from dm_document.
SELECT doc.object_name,fol.r_folder_path
FROM dm_document doc, dm_folder fol
WHERE any doc.i_folder_id = fol.r_object_id
The query looked very simple to me. He seemed to be happy and he told he will try it out. I was happy that I have given him a solution. I was a bit surprised when he told me that he was getting an error while executing this query. I didn’t expect this. The error was:
- :[DM_QUERY2_E_REPEAT_TYPE_JOIN]error: “Your query is selecting repeating attributes and joining types.”
The above query works fine if I select only doc.object_name; but it was not allowing me to select fol.r_folder_path which is a repeating attribute. The conclusion was that a repeating valued attribute cannot be selected in a normal query which uses repeating valued attribute in a join. It can be achieved by using DQL Hints mentioned later. But this friend of mine needs both Document Name and Folder Path together. I asked him to write a dfc code which gets all the documents, finds their folder path and writes this information to a file. But my friend is a real lazy guy. The bad part being that I am also no different.
I thought a lot and in the end I got an idea. I asked him to fire a query on the RDBMS tables. The object types are represented as _s and _r tables in RDBMS for their single and repeating valued attribute respectively. The query has to be fired on dm_document_s, dm_document_r, dm_folder_s and dm_folder_r tables.
So, the new query which I formed was:
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_document_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
But when I fire this query on my docbase I again got an error.
- :[DM_QUERY2_E_TABLE_NOT_FOUND]error: “The database table or view was not found in the database. Error from the database was: ‘ — The database object is invalid — STATE=S0002, CODE=208, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name ‘dbo.dm_document_r’.'”
I realized that there is no such table called dm_document_r as dm_document doesn’t have any repeating attribute of its own. But in that case from where should I get the value for i_folder_id? If i_folder_id is not an attribute of dm_document then it should be inherited from dm_sysobject. Isn’t so?
So I had my new dql:
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_document_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
I was so confident. This is the ultimate query which will get me the result. When I fired this query I got the following result.
- :[DM_QUERY_E_CURSOR_ERROR]error: “A database error has occurred during the creation of a cursor (‘ STATE=S0022, CODE=207, MSG=[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name ‘object_name’.’).”
How could I forget that object_name is also an attribute of dm_sysobject. Actually the dm_document_s has just one attribute and that is the r_object_id. As a matter of fact dm_document doesn’t have any attribute of its own. Then what is the use of having the object_type dm_document? I leave this question to be answered by you. This has to be my final query.
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
Ultimately I got the result without any errors. But I wasn’t too happy. Many records in object_name as well as r_folder_path were showing as empty.
I did a quick fix:
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring
The r_folder_path was not showing any empty records but the case was not so with object_name. Something clicked in my mind. My mind had found a flaw in the query which was looking fine to me till now. My friend was interested in documents; not the sysobjects. I was actually giving him a lot of garbage.
And thus I got my next query:
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_type = 'dm_document'
Cool….. My friend looks to be the happiest person on earth. He got the result he was looking for. But I still want to do one more small change.
SELECT doc_s.object_name,fol_r.r_folder_path
FROM dm_sysobject_s doc_s, dm_sysobject_r doc_r, dm_folder_r fol_r
WHERE doc_s.r_object_type = 'dm_document'
AND fol_r.r_folder_path is not nullstring
AND doc_s.r_object_id = doc_r.r_object_id
AND doc_r.i_folder_id = fol_r.r_object_id
The result is same as the earlier query but somehow I look more satisfied with this query. I am not sure how good or how efficient this query is but my friend got what he wanted and I was satisfied that I was able to help him out.
The story is not over yet. I just checked the sql equivalent of this query in the dql component of webtop.
Here is the result:
SELECT all doc_s.object_name, fol_r.r_folder_path
FROM dbo.dm_sysobject_s doc_s, dbo.dm_sysobject_r doc_r, dbo.dm_folder_r fol_r
WHERE ((doc_s.r_object_type=N'dm_document')
AND fol_r.r_folder_path != ' '
AND (doc_s.r_object_id=doc_r.r_object_id)
AND (doc_r.i_folder_id=fol_r.r_object_id))
Do you know what the most interesting part is?
After doing all this crap, I posted it on my blog. I had added tags like DQL, Documentum, etc. When I clicked on the DQL link (The tag which I had added) I got Rajendra’s blog in the result. He has posted a query there.
SELECT A.r_object_id, A.object_name, B.r_folder_path
FROM dm_document A, dm_folder_r B
WHERE any A.i_folder_id = B.r_object_id
Doesn’t that look much simple? :D:D:D
Here is another query from inthewoods. It uses DQL Hint and appears to be a better option than the earlier two.
SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM dm_document doc, dm_folder fld
WHERE doc.i_folder_id = fld.r_object_id ENABLE(ROW_BASED)
So.. Thats the whole story. Hope you enjoyed it.
*Guess it would be a nice idea to add ‘fld.r_folder_path is not nullstring’ and ‘order by r_object_id’ to the final query.
Hi Uttakarsh,
I have gone thru the DQL queries and found that they are very useful. And I was working on them and found some results.
You can try the following query to get document name and its folder path….
Select s.object_name, fr.r_folder_path From dm_document s,dm_sysobject_r sr,dm_sysobject_r sr3,dm_folder_r fr
Where s.i_is_deleted = 0
AND sr3.r_object_id = s.r_object_id
AND sr3.i_position = -1
AND sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND fr.i_position = -1
Order by fr.r_folder_path,s.object_name
Which will give u the list of all the documents with their folder paths.
If you want to restrict your query with the documents from a particular folder path and its downline (Documents in the parent folder as well as its subfolders)
Select s.object_name, fr.r_folder_path From dm_document s,dm_sysobject_r sr,dm_sysobject_r sr3,dm_folder_r fr
Where s.i_is_deleted = 0
AND sr3.r_object_id = s.r_object_id
AND sr3.i_position = -1
AND sr.r_object_id = s.r_object_id
AND fr.r_object_id = sr.i_folder_id
AND fr.i_position = -1
AND folder(‘/ENPC/Karthik’,descend)
Order by fr.r_folder_path,s.object_name
Using this query u will get the result that consists of all the documents under the folder Karthik which is in the cabinet ENPC.
Regards,
SRK
Excellent Post – Thank you.
I’m an not a great DQL guy (and also a bit lazy).
I want to return the folder path for a fixed number of documents.
I tried to include the list of r_object_ids (from dm_document) using the DQL with “AND doc.r_object_id IN(‘…’,’…’), but that doesn’t work.
Can you help me with this?
Many thanks
I am wondering why that query doesn’t work for you.
If I have understood you correctly you are looking for a query like …..
SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM my_doc doc, dm_folder fld
WHERE doc.r_object_id IN(select r_object_id from my_doc)
AND doc.i_folder_id = fld.r_object_id
AND fld.r_folder_path is NOT NULLSTRING
ENABLE(ROW_BASED)
You can add your own WHERE clause in the sub-query. You can also provide a list of objectIds instead. In any case it should work fine.
Cheers.
Thanks a million! I needed to this very thing — get object name + folder path — and I was finally able to do it with your model:
SELECT doc.r_object_id, doc.object_name, fld.r_folder_path
FROM my_doc doc, dm_folder fld
WHERE doc.r_object_id IN (select r_object_id from my_doc)
AND doc.i_folder_id = fld.r_object_id
AND fld.r_folder_path is NOT NULLSTRING
ENABLE(ROW_BASED)
Thanks again and cheers!
Hi Javier,
Its really good to know that it was useful to you.
Just one thing which I want to mention here…
“doc.r_object_id IN(select r_object_id from my_doc)”
The above part was added specifically to address Mark’s case. If you want all the results for my_doc and don’t want to filter any, it would be better to remove it. That part is redundant if it doesn’t use a WHERE clause.
Oh, I definitely needed that part as well, since I had to restrict my query to images for which any version is not expired. Here’s my query:
Select cons_img.r_object_id, cons_img.object_name, c_alt_tag, dm_folder.r_folder_path
FROM cons_img, dm_folder
WHERE cons_img.r_object_id not in (select r_object_id from cons_img where ANY r_version_label = ‘Expired’)
AND cons_img.i_folder_id = dm_folder.r_object_id
AND dm_folder.r_folder_path is NOT NULLSTRING
ENABLE(ROW_BASED)
Thanks again
I was wondering if you ever considered changing the layout of your website?
Its very well written; I love what youve got to say. But maybe you could a little more
in the way of content so people could connect with it better.
Youve got an awful lot of text for only having one or 2 images.
Maybe you could space it out better?
seems I am replying to your comment after three months. 🙂
It’s good to know that you liked it; and I appreciate your suggestion.
But I guess, I don’t have that energy in me to edit that post again. 🙂 Let me try to take care of it in future.
Thanks for your comments.
Very useful post. Thanks Uttkarsh.
Quality content is the main to attract the people to pay a visit the
web site, that’s what this site is providing.
Spot on with this write-up, I seriously feel this web site needs
far more attention. I’ll probably be returning to read more, thanks for the information!
Thanks a lot. I think I should get back at writing.. 🙂
This paragraph is inn fact a good one it helps new net visitors, who are wishing
for blogging.
Hi,
I have sililar kind of problem but inspite of r_folder_path, i have r_version_label attribute of dm_document.
I am applying left join between dm_document and my custom typt.
Please provide your valuable suggestion.
I am so sorry Arpit.. Looking at this after an year.. I shall try to be more consistent on my blog.. 🙂