| 5504 |
dpurdie |
1 |
-- Outer select is used to order the results
|
|
|
2 |
select * from (
|
|
|
3 |
(
|
|
|
4 |
-- Select package version and project details for packages that are in a project
|
|
|
5 |
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
|
|
|
6 |
FROM package_versions pv,
|
|
|
7 |
packages pkg,
|
|
|
8 |
projects proj,
|
|
|
9 |
release_tags rt,
|
|
|
10 |
release_content rc
|
|
|
11 |
WHERE pv.pkg_id = pkg.pkg_id
|
|
|
12 |
AND rc.rtag_id = rt.rtag_id
|
|
|
13 |
AND rc.pv_id = pv.pv_id
|
|
|
14 |
and proj.PROJ_ID = rt.PROJ_ID
|
|
|
15 |
AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
|
|
|
16 |
AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
|
|
|
17 |
)
|
|
|
18 |
UNION
|
|
|
19 |
(
|
|
|
20 |
-- Select package version and dummy data for all packages
|
|
|
21 |
-- Create a very low pv_id such that these appear before versions that are in a package
|
|
|
22 |
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
|
|
|
23 |
FROM package_versions pv,
|
|
|
24 |
packages pkg
|
|
|
25 |
WHERE pv.pkg_id = pkg.pkg_id
|
|
|
26 |
AND LOWER(pkg.pkg_name) LIKE LOWER('/*PKG_NAME*/')
|
|
|
27 |
AND NVL(LOWER(pv.v_ext),'LINK_A_NULL') LIKE NVL(LOWER('/*V_EXT*/'),'LINK_A_NULL')
|
|
|
28 |
)
|
|
|
29 |
)
|
|
|
30 |
ORDER BY UPPER(pkg_name), UPPER(v_ext), pv_id, UPPER(proj_name), rtag_id
|
|
|
31 |
|