Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
13 rsolanki 1 
/* Product Name Search */
2 
 
3 
SELECT DISTINCT
4 
	   qry.pkg_id,
5 
	   qry.pkg_name,
6 
	   MIN(qry.display_order) OVER (PARTITION BY qry.pkg_name) AS display_min,
7 
	   DECODE( qry.pkg_name,
8 
	   		   ':PROD_NAME', 'checked' ) AS checked
9 
  FROM (
10 
		/* Show Product with exact match */
11 
		SELECT pkg.pkg_id,
12 
			   pkg.pkg_name,
13 
			   1 AS display_order
14 
		  FROM PACKAGES pkg
15 
		 WHERE pkg.PKG_NAME = ':PROD_NAME'
16 
 
17 
		UNION
18 
 
19 
		/* Show Product with exact match (case ignored) */
20 
		SELECT pkg.pkg_id,
21 
			   pkg.pkg_name,
22 
			   2 AS display_order
23 
		  FROM PACKAGES pkg
24 
		 WHERE UPPER(pkg.PKG_NAME) = UPPER(':PROD_NAME')
25 
 
26 
		UNION
27 
 
28 
		/* Show Product starting with name  */
29 
		SELECT pkg.pkg_id,
30 
			   pkg.pkg_name,
31 
			   3 AS display_order
32 
		  FROM PACKAGES pkg
33 
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER(':PROD_NAME%')
34 
 
35 
 
36 
		UNION
37 
 
38 
		/* Show Product with name   */
39 
		SELECT pkg.pkg_id,
40 
			   pkg.pkg_name,
41 
			   4 AS display_order
42 
		  FROM PACKAGES pkg
43 
		 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER('%:PROD_NAME%')
44 
  	   ) qry
45 
ORDER BY display_min