Subversion Repositories DevTools

SELECT rel.*,
           LEVEL AS HIERARCHY
  FROM (
  
                SELECT rt.RTAG_ID, 
                       rt.RTAG_NAME, 
                           rt.DISPLAY_ORDER,
                       DECODE( rt.PARENT_RTAG_ID, 
               rt.RTAG_ID, 0, rt.PARENT_RTAG_ID ) AS PARENT_RTAG_ID,
               DECODE( rt.official ,
                       'Y', CASE WHEN TRUNC (SYSDATE - rt.official_stamp) > 60 THEN 'O' ELSE 'Y' END, 
                       rt.official ) as OFFICIAL,
                           rt.DESCRIPTION,
                           rt.ASSOC_MASS_REF,
               TO_CHAR(rt.created_stamp,'DD-Mon-YYYY') AS created_stamp,
               U2.full_name as creator,
               TO_CHAR(rt.official_stamp,'DD-Mon-YYYY') AS OFFICIAL_STAMP,
               TRUNC (SYSDATE - rt.official_stamp) as OFFICIAL_STAMP_DAYS,
               DECODE(U1.full_name, NULL, 'Unknown', U1.full_name )as modifier
                 FROM RELEASE_TAGS rt
               left outer join users U1 on rt.official_id = U1.user_id
               left outer join users U2 on rt.creator_id = U2.user_id
                 WHERE rt.PROJ_ID = :PROJ_ID
                ) rel
START WITH rel.PARENT_RTAG_ID = 0
CONNECT BY PRIOR rel.RTAG_ID = rel.PARENT_RTAG_ID   
ORDER SIBLINGS BY rel.DISPLAY_ORDER