how to fetch start by attribute in a connect by sql

Question

I am using recursive sql and I have a query like this

SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,parentfolderid
   FROM FOLDER_Table
   START WITH FOLDERID=12345 CONNECT by PRIOR PARENTFOLDERID=FOLDERID

how can I display the folderId i'm giving in FOLDERID=12345 in the select attributes? if I run below query

SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,parentfolderid, folderId
   FROM FOLDER_Table
   START WITH FOLDERID=12345 CONNECT by PRIOR PARENTFOLDERID=FOLDERID

I am getting folderId corresponding to root, but not 12345

Answer 1

You can use CONNECT_BY_ROOT to get the root of the hierarchy, which in this case - because of how you're traversing it - is the ID you specified:

SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,
  parentfolderid, CONNECT_BY_ROOT(folderId) as queried_folderid
FROM FOLDER_Table
START WITH FOLDERID=12345
CONNECT by PRIOR PARENTFOLDERID=FOLDERID;

Quick demo with some dummy data:

create table folder_table (folderid, parentfolderid, name) as
  select 12345, 1234, 'FolderE' from dual
  union all select 1234, 123, 'FolderD' from dual
  union all select 123, 12, 'FolderC' from dual
  union all select 12, 1, 'FolderB' from dual
  union all select 1, null, 'FolderA' from dual
  union all select 12346, null, 'FolderF' from dual
;

SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,
  parentfolderid, CONNECT_BY_ROOT(folderId) as queried_folderid
FROM FOLDER_Table
START WITH FOLDERID=12345
CONNECT by PRIOR PARENTFOLDERID=FOLDERID;

PATH                                               PARENTFOLDERID UERIED_FOLDERID
-------------------------------------------------- -------------- ---------------
FolderE\                                                     1234           12345
FolderD\FolderE\                                              123           12345
FolderC\FolderD\FolderE\                                       12           12345
FolderB\FolderC\FolderD\FolderE\                                1           12345
FolderA\FolderB\FolderC\FolderD\FolderE\                                    12345

If you're on 11gR2 or higher you could also use recursive subquery factoring instead of connect by syntax, which avoid the reversing:

WITH r (path, parentfolderid, queriedfolderid) AS (
  SELECT name ||'\', parentfolderid, folderid
  FROM folder_table
  WHERE folderid = 12345
  UNION ALL
  SELECT ft.name ||'\'|| r.path, ft.parentfolderid, r.queriedfolderid
  FROM r
  JOIN folder_table ft ON ft.folderid = r.parentfolderid
)
SELECT *
FROM r;

PATH                                               PARENTFOLDERID QUERIEDFOLDERID
-------------------------------------------------- -------------- ---------------
FolderE\                                                     1234           12345
FolderD\FolderE\                                              123           12345
FolderC\FolderD\FolderE\                                       12           12345
FolderB\FolderC\FolderD\FolderE\                                1           12345
FolderA\FolderB\FolderC\FolderD\FolderE\                                    12345

The anchor member gets your initial target row, and the recursive member prepends the next higher level's folder name, while passing through whatever other information you want to keep.