Thursday, June 13, 2013

in Oracle build path from parent to lasted child

WITH tb AS
  (SELECT Pck2.prnt_pck_id,
    PCK2.PCK_ID,
    Pck2.pck_nm,
    SYS_CONNECT_BY_PATH(Pck2.pck_nm, '.') pck_path
  FROM DDM_PCK Pck2
  WHERE 1                        = 1
  AND pck2.pjt_id                = 'PJT20120711000000001'
    START WITH Pck2.PRNT_PCK_ID IS NULL
    CONNECT BY prior Pck2.PCK_ID = Pck2.PRNT_PCK_ID
  )
SELECT tb.*,
  clss.clss_nm
FROM tb,
  ddm_clss clss
WHERE 1= 1
  --and clss.clss_id = 'DCL20121107000000001'
AND clss.PCK_ID = tb.PCK_ID


No comments :

Post a Comment