Subversion Repositories DevTools

/* Find in BOM */
SELECT DISTINCT
           qry.RESULT_NAME,
       qry.LOCATION,
       qry.URL,
           MIN(qry.display_order) OVER (PARTITION BY qry.RESULT_NAME) AS display_min
  FROM (

                /* Product Name at OS */
                SELECT pkg.PKG_NAME AS RESULT_NAME,
                       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / '|| pkg.PKG_NAME ||' '|| pv.PKG_VERSION  AS LOCATION,  
                       'ProdDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID ||'&prod_id='|| pv.PV_ID  AS URL,
                       1 AS DISPLAY_ORDER     
                  FROM OS_CONTENTS osc,
                           PACKAGES pkg,
                       PACKAGE_VERSIONS pv,
                       BOM_CONTENTS bc,
                       OPERATING_SYSTEMS os,
                       NETWORK_NODES nn
                 WHERE osc.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID
                   AND nn.NODE_ID = bc.NODE_ID
                   AND bc.BOM_ID = :BOM_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND osc.PROD_ID = pv.PV_ID  
                   AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )  
                    
                   
                UNION 
                    
                   
                /* Product Name at Base Env */
                SELECT pkg.PKG_NAME AS RESULT_NAME,
                       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME ||' / <br>'|| be.BASE_ENV_NAME ||'/'||pkg.PKG_NAME ||' '|| pv.PKG_VERSION  AS LOCATION,  
                       'Os_BaseConf.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID  AS URL,
                       2 AS DISPLAY_ORDER     
                  FROM BASE_ENV be,
                       BASE_ENV_CONTENTS bec,
                       OS_BASE_ENV obe,
                       NETWORK_NODES nn,
                       OPERATING_SYSTEMS os,
                       PACKAGES pkg,
                       PACKAGE_VERSIONS pv  
                 WHERE be.BOM_ID = :BOM_ID
                   AND bec.BASE_ENV_ID = be.BASE_ENV_ID
                   AND pv.PKG_ID = pkg.PKG_ID
                   AND bec.PROD_ID = pv.PV_ID
                   AND obe.BASE_ENV_ID = be.BASE_ENV_ID
                   AND obe.OS_ID = os.OS_ID
                   AND os.NODE_ID = nn.NODE_ID  
                   AND UPPER( pkg.PKG_NAME ) LIKE UPPER( :KEYWORD )   
                 
                   
                UNION   
                     
                   
                /* Network Node Name */
                SELECT nn.NODE_NAME AS RESULT_NAME,
                       'Location: '|| nn.NODE_NAME  AS LOCATION,  
                       'NodeDefault.asp?bom_id='|| :BOM_ID ||'&node_id='|| nn.NODE_ID  AS URL,
                       3 AS DISPLAY_ORDER     
                  FROM BOM_CONTENTS bc,
                       NETWORK_NODES nn
                 WHERE nn.NODE_ID = bc.NODE_ID
                   AND UPPER( nn.NODE_NAME ) LIKE UPPER( :KEYWORD ) 
                   AND bc.BOM_ID = :BOM_ID  
                   
                
                UNION
                
                
                /* OS Name */
                SELECT os.OS_NAME AS RESULT_NAME,
                       'Location: '|| nn.NODE_NAME ||' / '|| os.OS_NAME  AS LOCATION,  
                       'OsDefault.asp?bom_id='|| :BOM_ID ||'&os_id='|| os.OS_ID  AS URL,
                       4 AS DISPLAY_ORDER     
                  FROM BOM_CONTENTS bc,
                       OPERATING_SYSTEMS os,
                       NETWORK_NODES nn
                 WHERE os.NODE_ID = nn.NODE_ID
                   AND nn.NODE_ID = bc.NODE_ID
                   AND bc.BOM_ID = :BOM_ID
                   AND UPPER( os.OS_NAME ) LIKE UPPER( :KEYWORD )    

           ) qry
ORDER BY display_min