How to get the full path of objects when you only have the dataid.
1. First the main query return a list of rows with dataids.
2. With "CONNECT BY PRIOR parentid = dataid" we get from DTREE the needed list of dataid/parentid pair to each folder ascending to the root for each dataid of the main query.
3. With that subset of DTREE we construct the full path of each object of the main query as we would if we wanted to do it for the whole DTREE table.
4. Finally we join the main query with the previous query containing paths.
So to use it just replace the example main_query with your own. The only condition is that your query return a field named dataid.
Ps: Oracle only.
with main_query as ( select docid dataid, filename from dversdata where filetype = 'rtf' ), dataids_to_root as ( select distinct dataid, parentid, name from dtree a CONNECT BY PRIOR a.parentid = a.dataid start with dataid in (select dataid from main_query) ) , full_path as ( select dataid, SYS_CONNECT_BY_PATH(name, ':') path from dataids_to_root where dataid in (select dataid from main_query) CONNECT BY PRIOR dataid = parentid start with parentid = -1 )select main_query.*, full_path.*from full_pathinner join main_query on main_query.dataid = full_path.dataidorder by path
