Documentum DQL Getting list subfolders from a few folders

Question

everyone! I am starting with Documentum this month and here is my problem. There is multi-select drop-down list with cabinets. I have to choose some of them to get list of inner folders in my result list below. This query:

select * from dm_folder where folder(id($param$))

or

select * from dm_folder where folder($param$)

where param is object_name

works with single drop-down select. I've tried to insert "in"

select * from dm_folder where folder in($param$)

results with

[DM_QUERY_E_SYNTAX]error:  "A Parser Error (syntax error) has occurred in the vicinity of:  select * from dm_folder where folder in"

or

select * from dm_folder where folder(id in($param$))

results with

select * from dm_folder where folder(id in ('0c0511d48000105',' 0c0511d48000106'))

[DM_QUERY_E_SYNTAX]error:  "A Parser Error (syntax error) has occurred in the vicinity of:  select * from dm_folder where folder(id in"

and return multivalue flag in queries above but it doesn't work. Can someone help, please? Thank you!

can you add more details on how you tried to insert IN, and how does it not work? It's not clear what is the problem you are dealing with — Miki, Oct 02 '19 at 10:30
thanks for your comment i've edited post and added more information — issverg, Oct 02 '19 at 10:49
you are writing a query the wrong way, I'll write in an answer the correct format — Miki, Oct 02 '19 at 10:59
upper or lower case on keyword letters in query doesn't matter — Miki, Oct 02 '19 at 11:11
I've edited your question since you are a new user. Please validate that I didn't changed something with it — Miki, Oct 02 '19 at 11:37

Miki · Accepted Answer · 2019-10-03T07:43:47.863

2

Try with this one:

SELECT * FROM dm_folder WHERE r_object_id IN ('0c0511d48000105',' 0c0511d48000106')

I think it's clear where you made an error. Keyword FOLDER you used is function that accepts single value as parameter.

From DQL guide:

The FOLDER predicate
The FOLDER predicate identifies what folders to search. The syntax is:
[NOT] FOLDER(folder_expression {,folder_expression} [,DESCEND])
The folder_expression argument identifies a folder in the current repository. You cannot search a
remote folder (a folder that does not reside in the current repository). Valid values are:
• An ID function
• The ID function (described in The ID function, page 29) identifies a particular folder.
• A folder path
A folder path has the format:
/cabinet_name{/folder_name}
Enclose the path in single quotes. Because cabinets are a subtype of folder, you can specify a
cabinet as the folder.
• The keyword DEFAULT
The keyword DEFAULT directs the server to search the user’s default folder. Note that a user’s
default folder is the same as the user’s default cabinet (because cabinets are a subtype of folders).

Edit 1:

SELECT * FROM dm_folder WHERE ANY i_folder_id IN ('0c0511d48000105',' 0c0511d48000106')

With this query you are looking for folder type objects whose parent is any of the folders specified as the parameter.

Edit 1:

SELECT * FROM dm_folder WHERE i_cabinet_id IN (<list of ids>)

This will return you all folder objects under cabinet

edited Oct 03 '19 at 07:43

answered Oct 02 '19 at 11:00

Miki

2,493
2
27
39

Yes, I've also tried it before I found an example with FOLDER () selector... but it shows nothing. Although query executes successfully it says... – issverg Oct 02 '19 at 11:08
1

Well, syntax is OK now, but you need to work on semantics - what do you want to found out with this query? – Miki Oct 02 '19 at 11:09
Ok. So it seems that I can't use FOLDER, but what should I use? As you can see in my question post I use $param$ inputs that come from multi-select drop-down list. In single-select FOLDER works perfectly. But what should I use instead in this situation? – issverg Oct 02 '19 at 11:20
OK, I probably understand what are you trying to achieve. You are searching for folders under one or more specified folder. I'll edit the answer – Miki Oct 02 '19 at 11:26
Yes, exactly! Thank you! – issverg Oct 02 '19 at 11:27
1

I think this is what are you looking for. i_folder_id is a mutlivalue field storing id's of the parent folder of the object. object can be linked to multiple folders - there for this is a mutivalue field. That's why we need to use keyword ANY in where clause – Miki Oct 02 '19 at 11:32
Unfortunately still doesn't show anything, although query executes successfully. – issverg Oct 02 '19 at 11:46
Sure I'll accept!) Does it matter that in drop-down multi-select appears list of cabinets? And I need folder inside them? – issverg Oct 02 '19 at 11:49
1

Express yourself in the context of the query you specified. The list of cabinets you are talking about are the id's in the WHERE clause? – Miki Oct 02 '19 at 11:52
You mean the source of the list? It is (SELECT * FROM dm_cabinet) in list show object_name and as value comes out either r_object_id for example. – issverg Oct 02 '19 at 12:03
Then all should be fine, you are missing something here. It's up to you to find the problem. Maybe there are no any folder under those Cabinets? – Miki Oct 02 '19 at 12:24
Hmm, but this query ```select * from dm_folder where folder( id('0c0511d480000106'))``` shows me list of folders. – issverg Oct 02 '19 at 12:32
Hm, I don't have time right now to go in the details, will do it later. Try with one more thing for now: SELECT * FROM dm_folder WHERE i_cabinet_id IN () This will return you all folder under cabinet, but through whole folder structure, this can be problem for you – Miki Oct 02 '19 at 12:44
Thank you so much for your help and time it seems to be work! I'd check it browser and feed back. – issverg Oct 02 '19 at 12:53
When you will have time please update your answer, i'll accept it – issverg Oct 02 '19 at 14:35

Documentum DQL Getting list subfolders from a few folders

1 Answers1