Subversion Repositories DevTools

SELECT qry.*
  FROM (
           
                /* PROJECTS */
                SELECT DECODE ( COUNT( br.branch_id ),
                                                0, NULL,
                                                NULL, NULL,
                                                DECODE ( parent.proj_id,
                                                                 :proj_id, '-',
                                                                 '+'
                                                           )
                                          ) AS expand,
                           parent.proj_id,
                           parent.proj_name,
                           0 AS branch_id,
                           '-' AS branch_name,
               NULL AS branch_comments
                  FROM BRANCHES br,
                           (
                                SELECT proj.proj_id,
                                           proj.PROJ_NAME
                                  FROM DM_PROJECTS proj  
                                 WHERE proj.IS_HIDDEN IS NULL
                           ) parent
                 WHERE br.proj_id (+) = parent.proj_id     
                 GROUP BY parent.proj_id,
                              parent.proj_name

                UNION
                                  
                /* BRANCHES */
                SELECT DECODE ( COUNT( bm.bom_id ),
                                                0, NULL,
                                                NULL, NULL,
                                                DECODE ( parent.branch_id,
                                                                 :branch_id, '-',
                                                                 '+'
                                                           )
                                          ) AS expand,
                           parent.proj_id,
                           parent.proj_name,
                           parent.branch_id,
                           parent.branch_name,
               parent.BRANCH_COMMENTS
                  FROM BOMS bm,
                           (
                                SELECT proj.proj_id,
                                           proj.PROJ_NAME,
                                           br.branch_id,
                                           br.BRANCH_NAME,
                       br.BRANCH_COMMENTS
                                  FROM DM_PROJECTS proj,
                                           BRANCHES br
                                 WHERE br.proj_id = proj.proj_id
                                   AND proj.proj_id = :proj_id
                                   AND proj.IS_HIDDEN IS NULL
                                   AND br.IS_HIDDEN IS NULL
                           ) parent
                 WHERE bm.branch_id (+) = parent.branch_id         
                 GROUP BY parent.proj_id,
                              parent.proj_name,
                                  parent.branch_id,
                              parent.branch_name,
                  parent.BRANCH_COMMENTS                                  
                                  
                                  
                  ) qry
 ORDER BY UPPER(qry.proj_name), qry.branch_id