Subversion Repositories DevTools

/* Product Name Search */

SELECT DISTINCT
           qry.pkg_id,
           qry.pkg_name,
           MIN(qry.display_order) OVER (PARTITION BY qry.pkg_name) AS display_min,
           DECODE( qry.pkg_name, 
                           ':PROD_NAME', 'checked' ) AS checked
  FROM (
                /* Show Product with exact match */
                SELECT pkg.pkg_id,
                           pkg.pkg_name,
                           1 AS display_order
                  FROM PACKAGES pkg
                 WHERE pkg.PKG_NAME = ':PROD_NAME'
                
                UNION
                
                /* Show Product with exact match (case ignored) */ 
                SELECT pkg.pkg_id,
                           pkg.pkg_name,
                           2 AS display_order
                  FROM PACKAGES pkg
                 WHERE UPPER(pkg.PKG_NAME) = UPPER(':PROD_NAME') 
                 
                UNION
                
                /* Show Product starting with name  */ 
                SELECT pkg.pkg_id,
                           pkg.pkg_name,
                           3 AS display_order
                  FROM PACKAGES pkg
                 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER(':PROD_NAME%')  
                
                 
                UNION
                
                /* Show Product with name   */ 
                SELECT pkg.pkg_id,
                           pkg.pkg_name,
                           4 AS display_order
                  FROM PACKAGES pkg
                 WHERE UPPER(pkg.PKG_NAME) LIKE UPPER('%:PROD_NAME%')              
           ) qry
ORDER BY display_min