Oracle Data-Tree

here is a simple example for the CONNECT BY clause.

tom is the manager of peter and simon
and simon is the manager of greg (see the sub-select)

first all possible trees

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“
    , LEVEL    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

now select only the tree from root (START WITH)

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
    , LEVEL
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, NULL AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
START WITH EMP_ID = 1
CONNECT BY PRIOR EMP_ID = MAN_ID
ORDER BY EMP_ID
;

here with a cycle, when tom is his own manager. now the NOCYCLE clause is needed and with CONNECT_BY_ISCYCLE you can find all cycles.

SELECT 
    EMP_ID
    , LAST_NAME
    , MAN_ID
    , LEVEL AS myLEVEL
    , CONNECT_BY_ISCYCLE „Cycle“
    , SYS_CONNECT_BY_PATH(LAST_NAME, ‚/‘) „Path“    
FROM
(
    SELECT 1 AS EMP_ID, ‚tom‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 2 AS EMP_ID, ‚peter‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 3 AS EMP_ID, ’simon‘ AS LAST_NAME, 1 AS MAN_ID FROM DUAL
    UNION ALL
    SELECT 4 AS EMP_ID, ‚greg‘ AS LAST_NAME, 2 AS MAN_ID FROM DUAL
)
CONNECT BY NOCYCLE PRIOR EMP_ID = MAN_ID
ORDER BY LEVEL
;

Advertisements

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s