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:
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 || ':'
):
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
| Liste Membres | LEVEL |
|---------------|-------|
| :1:20:120 | 4 |