How to get list of all manager using hierarchical query

Question

I would like to pull list of employees who are managers of me. Have a look at below query:

SELECT   SYS_CONNECT_BY_PATH (username, ':') AS "Liste Membres",LEVEL
  FROM   employees
 WHERE   CONNECT_BY_ISLEAF = 1
         AND username = '150') -- My_code
START WITH   manager IS NULL
CONNECT BY   PRIOR username = manager

The result of this query is:

:1:20:120:150

The result that I want to get:

:1:20:120:

Why don't you just strip youe username of? `SUBSTR(SYS_CONNECT_BY_PATH (username, ':'), 1, LENGTH(SYS_CONNECT_BY_PATH (username, ':'))-LENGTH(username))` — Radagast81, Sep 07 '18 at 09:47

score 1 · Accepted Answer · answered Sep 07 '18 at 09:47

Change it to SYS_CONNECT_BY_PATH (manager, ':') and then remove the leading : that is appended for the root level NULL manager (and if you want the trailing : then add || ':'):

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE EMPLOYEES ( manager, username ) AS
  SELECT NULL, 1 FROM DUAL UNION ALL
  SELECT 1, 20 FROM DUAL UNION ALL
  SELECT 20, 120 FROM DUAL UNION ALL
  SELECT 120, 150 FROM DUAL;

Query 1:

SELECT   SUBSTR( SYS_CONNECT_BY_PATH (manager, ':'), 2 ) AS "Liste Membres",
         LEVEL
FROM     employees
WHERE    CONNECT_BY_ISLEAF = 1
AND      username = '150' -- My_code
START WITH   manager IS NULL
CONNECT BY   PRIOR username = manager

Results:

| Liste Membres | LEVEL |
|---------------|-------|
|     :1:20:120 |     4 |

How to get list of all manager using hierarchical query

1 Answers1