Subversion Repositories DevTools

-- Outer select is used to order the results
select * from (
        (
          -- Select package version and project details for packages that are in a project
          SELECT pkg.pkg_name as pkg_name, pv.pkg_version, proj.proj_name, 'xxx' as vtree_name, rt.rtag_name, rt.rtag_id, pv.pv_id, pv.v_ext as v_ext
          FROM package_versions pv,
                   packages pkg,
                   projects proj,
                   release_tags rt,
                   release_content rc
         WHERE pv.pkg_id = pkg.pkg_id
           AND rc.rtag_id = rt.rtag_id
           AND rc.pv_id = pv.pv_id
           and proj.PROJ_ID = rt.PROJ_ID
           AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
           AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
        )
UNION
        (
          -- Select package version and dummy data for all packages
          -- Create a very low pv_id such that these appear before versions that are in a package
          SELECT unique pkg.pkg_name as pkg_name, pv.v_ext, null as proj_name, null as vtree_name, null as rtag_name, null as rtag_id, -1 as pv_id , pv.v_ext as v_ext
          FROM package_versions pv,
                   packages pkg
         WHERE pv.pkg_id = pkg.pkg_id
           AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
           AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
        )
)
ORDER BY UPPER(pkg_name), UPPER(v_ext), pv_id, UPPER(proj_name), rtag_id