Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
6546 dpurdie 1 
/* Find release name */
2 
SELECT DISTINCT
3 
	   MIN(qry.DISPLAY_ORDER) OVER (PARTITION BY qry.RTAG_NAME) AS DISPLAY_MIN,
4 
	   qry.RTAG_ID,
5 
	   qry.RTAG_NAME,
6 
       qry.proj_name,
7 
       qry.proj_id,
8 
       qry.official
9 
  FROM (
10 
 
11 
  		/* Exact match */
12 
  		SELECT 1 AS DISPLAY_ORDER,
13 
			   rt.RTAG_ID,
14 
			   rt.RTAG_NAME,
15 
               p.proj_name,
16 
               p.proj_id,
17 
               rt.official
18 
		  FROM RELEASE_TAGS rt, PROJECTS p
19 
		 WHERE UPPER(rt.RTAG_NAME) = UPPER(:KEYWORD)
20 
         AND   p.proj_id = rt.proj_id
21 
 
22 
		UNION
23 
 
24 
		/* Starts with */
25 
		SELECT 2 AS DISPLAY_ORDER,
26 
			   rt.RTAG_ID,
27 
			   rt.RTAG_NAME,
28 
               p.proj_name,
29 
               p.proj_id,
30 
               rt.official
31 
		  FROM RELEASE_TAGS rt, PROJECTS p
32 
		 WHERE UPPER(rt.RTAG_NAME) LIKE UPPER(:KEYWORD) ||'%'
33 
         AND   p.proj_id = rt.proj_id
34 
 
35 
		UNION
36 
 
37 
		/* Ends with */
38 
		SELECT 3 AS DISPLAY_ORDER,
39 
			   rt.RTAG_ID,
40 
			   rt.RTAG_NAME,
41 
               p.proj_name,
42 
               p.proj_id,
43 
               rt.official
44 
		  FROM RELEASE_TAGS rt, PROJECTS p
45 
		 WHERE UPPER(rt.RTAG_NAME) LIKE '%'|| UPPER(:KEYWORD)
46 
         AND   p.proj_id = rt.proj_id
47 
 
48 
		UNION
49 
 
50 
		/* Middle of word */
51 
		SELECT 4 AS DISPLAY_ORDER,
52 
			   rt.RTAG_ID,
53 
			   rt.RTAG_NAME,
54 
               p.proj_name,
55 
               p.proj_id,
56 
               rt.official
57 
		  FROM RELEASE_TAGS rt, PROJECTS p
58 
		 WHERE UPPER(rt.RTAG_NAME) LIKE '%'|| UPPER(:KEYWORD) ||'%'
59 
         AND   p.proj_id = rt.proj_id
60 
 
61 
       ) qry
62 
ORDER BY DISPLAY_MIN, UPPER(qry.RTAG_NAME)
63