DQL to get folder and file name

amruthcheralu
amruthcheralu
edited April 2, 2015 in Documentum #1

I need help in getting the folder and the file name from documentum with the help of i_folder_id.

My query :

SELECT

  r_folder_path

FROM

  dm_folder

WHERE

  r_object_id in (select i_folder_id from t_pub_document search document contains '55000')

This gives me the folder path but I alse need the file names which has text 55000.

I tried this but no luck I get error from web service.

select s.object_name, f.r_folder_path

from t_pub_document s, dm_folder f

where s.i_folder_id = f.r_object_id

and s.i_folder_id in (select  i_folder_id from t_pub_document search document contains '55000'))

Please help.

Tagged:

Best Answer

  • Jeremy Saumen
    Jeremy Saumen Member
    edited April 2, 2015 #2 Answer ✓

    Hi,

    first of all : i_folder_id is a repeating, you need the keyword "any" to be able to query it.

    second : r_folder_path is also a repeating and a restriction of DQL is that you can't make a join and put a repeating in the select clause at the same time.

    What you can do is something similar to this :

    select d.r_object_id, d.object_name, f.r_folder_path from (select r_object_id, r_folder_path from dm_folder) f, (select r_object_id, object_name, i_folder_id from dm_document where FOLDER('/Temp',DESCEND)) d where f.r_object_id = d.i_folder_id and f.r_folder_path is not null;

Answers

  • Jeremy Saumen
    Jeremy Saumen Member
    edited April 2, 2015 #3 Answer ✓

    Hi,

    first of all : i_folder_id is a repeating, you need the keyword "any" to be able to query it.

    second : r_folder_path is also a repeating and a restriction of DQL is that you can't make a join and put a repeating in the select clause at the same time.

    What you can do is something similar to this :

    select d.r_object_id, d.object_name, f.r_folder_path from (select r_object_id, r_folder_path from dm_folder) f, (select r_object_id, object_name, i_folder_id from dm_document where FOLDER('/Temp',DESCEND)) d where f.r_object_id = d.i_folder_id and f.r_folder_path is not null;

  • amruthcheralu
    amruthcheralu
    edited April 2, 2015 #4

    Thanks a lot that idea worked out for us. New query :

    select d.r_object_id, d.object_name, f.r_folder_path from (select r_object_id, r_folder_path from dm_folder) f, (select r_object_id, object_name, i_folder_id from t_pub_document search document contains '55000') d where f.r_object_id = d.i_folder_id and f.r_folder_path is not null;

Categories