Fetching Folder Hierarchy in ascending order

Hello

I have a case where i want to fetch the folder hierarchy starting from child folder to parent folder to cabinet . in this case i have written a query


select object_name from dm_folder where r_object_id IN
(select i_ancestor_id from dm_folder where r_object_id='0b003fd18008614f');


results are in unordered fashion like this



Aspect              



Modules             



scase         



System               




but the original r_folder_path is




/System/Modules/Aspect/scase                                                                                                                                                                                                                                                                                                     





how to modify this query or is there ant different query to fetch this in ordered fashion
Regards

Find more posts tagged with

Comments

PanfilovAB

You can't achieve your requirement using DQL only

Mayur_shivs

By which way then i can achieve this

Sri_Kumaran

SELECT distinct f.r_object_id, fr.r_folder_path, f.r_version_label
FROM dm_sysobject_r sr,dm_folder_r fr, fdk_document f
WHERE sr.i_position = -1 AND
      fr.r_object_id = sr.i_folder_id AND
      fr.i_position = -1 AND
      f.r_object_id = sr.r_object_id AND
      f.i_latest_flag=1 AND
      f.i_is_deleted = 0 AND
      f.r_object_id IN (SELECT r_object_id
                        FROM fdk_document
                        WHERE lower(f.document_unit) LIKE 'abc%')
ORDER BY fr.r_folder_path,f.object_name DESC
enable(ROW_BASED)

NOTE: r_version_label is one of the repeating attributes of fdk_document (dm_document)

Output of above code:

f.r_object_id   fr.r_folder_path   f.r_version_label
-------------         ----------------       -----------------
xxxxxxxxxxxx    yyyyyyyyyyyyyyy    1.0
xxxxxxxxxxxx    yyyyyyyyyyyyyyy    APPROVED
xxxxxxxxxxxx    yyyyyyyyyyyyyyy    LATEST
xxxxxxxxxxxx    yyyyyyyyyyyyyyy    CURRENT

Expected output:

f.r_object_id   fr.r_folder_path   f.r_version_label
-------------       ----------------        -----------------
xxxxxxxxxxxx    yyyyyyyyyyyyyyy    1.0,APPROVED,LATEST,CURRENT