Subversion Repositories DevTools

Rev

Rev 6612 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
119 ghuddy 1 
SELECT *
2 
  FROM (
3 
        /* Hidden Views */
7030 dpurdie 4 
        SELECT 0 AS show,
119 ghuddy 5 
               vi.view_id,
6 
               vi.view_name,
6612 dpurdie 7 
               usr.full_name AS owner_name,
119 ghuddy 8 
               usr.user_email AS owner_email,
9 
               vi.base_view,
10 
               Count(vd.pkg_id) AS pkg_count,
11 
               vi.owner_id
12 
          FROM views vi, view_def vd, users usr
13 
         WHERE (vi.owner_id = 0 OR vi.public_read='Y')
14 
           AND usr.user_id = vi.owner_id
6586 dpurdie 15 
           AND vi.base_view != 'Y'
119 ghuddy 16 
           AND vi.view_id = vd.view_id(+)
17 
           AND vi.view_id NOT IN (SELECT vis.view_id FROM view_settings vis WHERE vis.user_id = $USER_ID$ )
7030 dpurdie 18 
          GROUP BY vi.view_id, vi.view_name, usr.full_name, usr.user_email, vi.base_view, vi.owner_id
119 ghuddy 19 
        UNION
20 
        /* Shown Views */
7030 dpurdie 21 
        SELECT 1 AS show,
119 ghuddy 22 
               vi.view_id,
23 
               vi.view_name,
6612 dpurdie 24 
               usr.full_name AS owner_name,
119 ghuddy 25 
               usr.user_email AS owner_email,
26 
               vi.base_view,
27 
               Count(vd.pkg_id) AS pkg_count,
28 
               vi.owner_id
29 
          FROM views vi, view_settings vis, view_def vd, users usr
30 
         WHERE vi.view_id = vis.view_id
31 
               AND usr.user_id = vi.owner_id
32 
               AND vi.view_id = vd.view_id(+)
33 
               AND vis.user_id = $USER_ID$
34 
               AND vi.public_read = 'Y'
6586 dpurdie 35 
               AND vi.base_view != 'Y'
6612 dpurdie 36 
         GROUP BY vi.view_id, vi.view_name, usr.full_name, usr.user_email, vi.base_view, vi.owner_id
6586 dpurdie 37 
        UNION
38 
        /* Base Views */
7030 dpurdie 39 
        SELECT NVL2(vis.view_id, 1,  0) AS show,
6586 dpurdie 40 
               vi.view_id,
41 
               vi.view_name,
42 
               '' AS owner_name,
43 
               '' AS owner_email,
44 
               vi.base_view,
45 
               Count(pv.pkg_id) AS pkg_count,
46 
 
7030 dpurdie 47 
          FROM views vi, release_content rc ,
48 
                    package_versions pv ,
49 
                     view_settings vis
6586 dpurdie 50 
         WHERE  vi.base_view = 'Y'
6588 dpurdie 51 
               AND rc.base_view_id(+) = vi.view_id
52 
               AND rc.pv_id = pv.pv_id(+)
7030 dpurdie 53 
               AND vi.view_id = vis.view_id(+)
54 
               AND vis.user_id(+) = $USER_ID$
55 
         GROUP BY vis.view_id, vi.view_id, vi.view_name, vi.base_view
119 ghuddy 56 
       ) pview
6586 dpurdie 57 
 ORDER BY UPPER(pview.view_name) ASC
7030 dpurdie 58