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

A small data sample that still reflects all the complications of the real problem, the logic of the requirement, and the output from the sample data are much more useful than posting your code attempt. You are asking us to guess the problem from an answer that doesn't work, and that doesn't make much sense, does it? — , Nov 07 '16 at 19:37

Alex Poole · Accepted Answer · 2016-11-07T19:48:09.273

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.

Kacper · Answer 2 · 2016-11-07T19:31:27.103

0

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

Or just

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

Is this what you need?

Sub query so I'd go for:

with subquery as (/*YOUR SUBQUERY*/)
select * from (
SELECT REVERSE(SYS_CONNECT_BY_PATH(REVERSE(name), '\')) as Path,parentfolderid, 12345 as folderId
       FROM FOLDER_Table
       START WITH FOLDERID=(select 1 from subquery) CONNECT by PRIOR PARENTFOLDERID=FOLDERID), subquery;

edited Nov 07 '16 at 19:31

answered Nov 07 '16 at 19:21

Kacper

4,798
2
19
34

I have a scenario where I'll be using a sub query in the start with. In that case how would you put :start_folder – dreambigcoder Nov 07 '16 at 19:27
@dvanwesh not the nicest solution but maybe will be useful for you – Kacper Nov 07 '16 at 19:31

how to fetch start by attribute in a connect by sql

2 Answers2