04-08-2020 08:38 AM - edited 05-08-2020 05:59 AM
I have a personnel table with employees which specifies managers.
I'd like to derive a table with all downstream reports for every employee.
The org breakdown could be like this:
MANAGER, EMPLOYEE
-------------------------
Using CONNECT BY, I can get the paths:
What I want is an ancestry table.
For the CEO, ancestry rows could look like this:
ANCESTOR, EMPLOYEE
-------------------------
Solved! Go to Solution.
05-08-2020 05:56 AM - edited 05-08-2020 05:58 AM
This seems to do what I want without LUA code... Welcome feedback.
ANCESTOR, EMPLOYEE
-------------------------
CEO, JACK
CEO, JANE
CEO, JILL
CEO, MARY
CEO, TOM
JACK, MARY
JACK, TOM
JILL, JANE
CREATE TABLE PARTY (
ACCOUNTID VARCHAR(50),
PARTYID VARCHAR(50)
);
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','CEO');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JACK');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JILL');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','TOM');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','MARY');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JANE');
CREATE TABLE RELATIONSHIP (
ACCOUNTID VARCHAR(50),
PARENT_PARTYID VARCHAR(50),
PARTYID VARCHAR(50)
);
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','CEO','JACK');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','CEO','JILL');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JACK','TOM');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JACK','MARY');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JILL','JANE');
WITH RELATIONSHIP_HIERARCHY AS (
SELECT ACCOUNTID, R.PARTYID DESCENDANTID,
'{"ascendants":["' || SYS_CONNECT_BY_PATH (R.PARENT_PARTYID,'","') || '"]}' ASCENDANT_PATH_JSON
FROM RELATIONSHIP R
CONNECT BY NOCYCLE PRIOR R.PARTYID = R.PARENT_PARTYID
)
SELECT DISTINCT ACCOUNTID, ASCENDANTID, DESCENDANTID
FROM (
SELECT ACCOUNTID, DESCENDANTID,
JSON_EXTRACT(
ASCENDANT_PATH_JSON,
'$.ascendants#'
)
EMITS (
ASCENDANTID VARCHAR(50)
)
FROM RELATIONSHIP_HIERARCHY
)
WHERE ASCENDANTID IS NOT NULL
--UNION SELECT ACCOUNTID, PARTYID ASCENDANTID, PARTYID DESCENDANTID FROM PARTY
ORDER BY ACCOUNTID, ASCENDANTID,DESCENDANTID;
05-08-2020 05:56 AM - edited 05-08-2020 05:58 AM
This seems to do what I want without LUA code... Welcome feedback.
ANCESTOR, EMPLOYEE
-------------------------
CEO, JACK
CEO, JANE
CEO, JILL
CEO, MARY
CEO, TOM
JACK, MARY
JACK, TOM
JILL, JANE
CREATE TABLE PARTY (
ACCOUNTID VARCHAR(50),
PARTYID VARCHAR(50)
);
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','CEO');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JACK');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JILL');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','TOM');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','MARY');
INSERT INTO PARTY (ACCOUNTID,PARTYID) VALUES ('EXASOL','JANE');
CREATE TABLE RELATIONSHIP (
ACCOUNTID VARCHAR(50),
PARENT_PARTYID VARCHAR(50),
PARTYID VARCHAR(50)
);
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','CEO','JACK');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','CEO','JILL');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JACK','TOM');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JACK','MARY');
INSERT INTO RELATIONSHIP (ACCOUNTID,PARENT_PARTYID,PARTYID) VALUES ('EXASOL','JILL','JANE');
WITH RELATIONSHIP_HIERARCHY AS (
SELECT ACCOUNTID, R.PARTYID DESCENDANTID,
'{"ascendants":["' || SYS_CONNECT_BY_PATH (R.PARENT_PARTYID,'","') || '"]}' ASCENDANT_PATH_JSON
FROM RELATIONSHIP R
CONNECT BY NOCYCLE PRIOR R.PARTYID = R.PARENT_PARTYID
)
SELECT DISTINCT ACCOUNTID, ASCENDANTID, DESCENDANTID
FROM (
SELECT ACCOUNTID, DESCENDANTID,
JSON_EXTRACT(
ASCENDANT_PATH_JSON,
'$.ascendants#'
)
EMITS (
ASCENDANTID VARCHAR(50)
)
FROM RELATIONSHIP_HIERARCHY
)
WHERE ASCENDANTID IS NOT NULL
--UNION SELECT ACCOUNTID, PARTYID ASCENDANTID, PARTYID DESCENDANTID FROM PARTY
ORDER BY ACCOUNTID, ASCENDANTID,DESCENDANTID;
Then it's time to become part of a unique family! Discover helpful tips and support other Community members with your knowledge.
Sign In