Subversion Repositories DevTools

Rev

Go to most recent revision | Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
119 ghuddy 1 
/* Find package name */
2 
SELECT DISTINCT
3 
	   MIN(qry.DISPLAY_ORDER) OVER (PARTITION BY qry.PKG_NAME) AS DISPLAY_MIN,
4 
	   qry.PKG_ID,
5 
	   qry.PKG_NAME
6 
  FROM (
7 
 
8 
  		/* Exact match */
9 
  		SELECT 1 AS DISPLAY_ORDER,
10 
			   pkg.PKG_ID,
11 
			   pkg.PKG_NAME
12 
		  FROM PACKAGES pkg
13 
		 WHERE UPPER(pkg.PKG_NAME) = UPPER(:KEYWORD)
14 
 
15 
		UNION
16 
 
17 
		/* Starts with */
18 
		SELECT 2 AS DISPLAY_ORDER,
19 
			   pkg.PKG_ID,
20 
			   pkg.PKG_NAME
21 
		  FROM PACKAGES pkg
22 
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER(:KEYWORD) ||'%'
23 
 
24 
		UNION
25 
 
26 
		/* Ends with */
27 
		SELECT 3 AS DISPLAY_ORDER,
28 
			   pkg.PKG_ID,
29 
			   pkg.PKG_NAME
30 
		  FROM PACKAGES pkg
31 
		 WHERE UPPER(pkg.PKG_NAME) LIKE '%'|| UPPER(:KEYWORD)
32 
 
33 
		UNION
34 
 
35 
		/* Middle of word */
36 
		SELECT 4 AS DISPLAY_ORDER,
37 
			   pkg.PKG_ID,
38 
			   pkg.PKG_NAME
39 
		  FROM PACKAGES pkg
40 
		 WHERE UPPER(pkg.PKG_NAME) LIKE '%'|| UPPER(:KEYWORD) ||'%'
41 
 
42 
       ) qry
43 
ORDER BY DISPLAY_MIN, UPPER(qry.PKG_NAME)
44