[CTE] 계층구조 쿼리

WITH BOM_CTE(PGM_PARENT, PGM_ID, LEVEL)
AS
(
    SELECT T.PGM_PARENT, T.PGM_ID, 1 AS LEVEL
 FROM ZAA210T T
 WHERE T.PGM_ID in ('root')

    UNION ALL
 SELECT C.PGM_PARENT, C.PGM_ID, LEVEL+1
 FROM ZAA210T C
 INNER JOIN BOM_CTE P
 ON C.PGM_PARENT = P.PGM_ID
)
SELECT A.PGM_PARENT, A.PGM_ID, MAX(A.LEVEL) AS LEVEL
FROM BOM_CTE A
GROUP BY A.PGM_PARENT, A.PGM_ID