Subversion Repositories DevTools

/* Process Name Search */

SELECT DISTINCT
           qry.proc_id,
           qry.proc_name,
                 qry.run_as,
           qry.pkg_owner,
           qry.is_interface,    
           MIN(qry.display_order) OVER (PARTITION BY qry.proc_name) AS display_min,
           DECODE( qry.proc_name, 
                           ':PROCESS_NAME', 'checked' ) AS checked
  FROM (
                /* Show Process with exact match */
                SELECT prc.proc_id,
                           prc.proc_name,
                           prc.run_as,
                           prc.pkg_owner,
                           prc.is_interface,            
                           1 AS display_order
                  FROM PROCESSES prc
                 WHERE prc.PROC_NAME = ':PROCESS_NAME'
                
                UNION
                
                /* Show Process with exact match (case ignored) */ 
                SELECT prc.proc_id,
                           prc.proc_name,
                           prc.run_as,
                           prc.pkg_owner,
                           prc.is_interface,    
                           2 AS display_order
                  FROM PROCESSES prc
                 WHERE UPPER(prc.PROC_NAME) = UPPER(':PROCESS_NAME') 
                 
                UNION
                
                /* Show Process starting with name  */ 
                SELECT prc.proc_id,
                           prc.proc_name,
                           prc.run_as,
                           prc.pkg_owner,
                           prc.is_interface,    
                           3 AS display_order
                  FROM PROCESSES prc
                 WHERE UPPER(prc.PROC_NAME) LIKE UPPER(':PROCESS_NAME%')  
                
                 
                UNION
                
                /* Show Process with name   */ 
                SELECT prc.proc_id,
                           prc.proc_name,
                           prc.run_as,
                           prc.pkg_owner,
                           prc.is_interface,    
                           4 AS display_order
                  FROM PROCESSES prc
                 WHERE UPPER(prc.PROC_NAME) LIKE UPPER('%:PROCESS_NAME%')          
           ) qry
ORDER BY display_min