Solved: How to derive ancestry rows from parent-child relationships

[drumcircle]

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

-------------------------

• CEO, JACK
• CEO, JILL
• JACK, TOM
• JACK, MARY
• JILL, JANE

Using CONNECT BY, I can get the paths:

• CEO, JACK
• CEO, JACK, TOM
• CEO, JACK, MARY
• CEO, JILL
• CEO, JILL, JANE

What I want is an ancestry table.

For the CEO, ancestry rows could look like this:

ANCESTOR, EMPLOYEE

-------------------------

• CEO, JACK
• CEO, JILL
• CEO, TOM
• CEO, MARY
• CEO, JANE

[drumcircle · Accepted solution]

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;