4 Replies Latest reply: Sep 16, 2013 6:37 PM by JeromeFr RSS

    List all interfaces that use a certain option on the KM

    knayam247
      Hi all,

      I'm trying to (using work repository tables) get a list of all interfaces using that have one or more options turned "on" for the relevant knowledge module. For example, we have IKM Oracle Incremental Update being used across 200+ interfaces.This KM has an option of FLOW_CONTROL which is set to True for some of these interfaces and False for the rest. Is there a query we can write against the repository tables to get us the list of interfaces/tables that have the option set to True?

      Any information would be really helpful. Thanks.
        • 1. Re: List all interfaces that use a certain option on the KM
          JeromeFr

          Hi,

          You can try with something like this :

           

          SELECT proj.project_name, folder.folder_name, int.POP_NAME interface_name, km.trt_name ikm, opt.ue_name option_name, int_opt.short_value, int_opt.i_txt_value
            FROM DEV_ODI_REPO.SNP_PROJECT proj, DEV_ODI_REPO.SNP_FOLDER folder, DEV_ODI_REPO.SNP_POP int, DEV_ODI_REPO.snp_trt km, DEV_ODI_REPO.snp_user_exit opt, DEV_ODI_REPO.snp_ue_used int_opt
            WHERE proj.i_project = folder.i_project
              AND folder.i_folder = int.i_folder
              AND km.i_trt = int.i_trt_kim
              AND km.i_trt = opt.i_trt
              AND int.i_pop = int_opt.i_instance
              AND opt.i_user_exit = int_opt.i_user_exit
              AND opt.ue_name = 'FLOW_CONTROL'
            ORDER BY km.trt_name;
          
          

           

          This will display the project, folder, the interface name, the IKM used, the option and its values (boolean or text).

          As you can see, I made the join on the ikm (km.i_trt = int.i_trt_kim). You might need one more table if you want to do it on LKMs.


          Hope it helps.

          Regards,
          JeromeFr

           

          Message was edited by: JeromeFr Forgot a join condition between snp_user_exit and snp_ue_used on i_user_exit columns

          • 2. Re: List all interfaces that use a certain option on the KM
            knayam247
            Hi Jerome. Thanks for your input. I'm only doing it on IKMs and this query gets exactly that :) Thanks again
            • 3. Re: List all interfaces that use a certain option on the KM
              JeromeFr
              You're welcome, glad it helped :)
              • 4. Re: List all interfaces that use a certain option on the KM
                JeromeFr

                Being back on this thread by pure hazard (I was looking for the same query but too lazy to write it myself... I forgot that I already wrote it...), I see a huge mistake. I forgot one join condition between snp_user_exit and snp_ue_used. I will edit my first reply so future reader can find the correct answer.

                 

                All my apologies.

                 

                Regards,

                JeromeFr

                 

                /!\ Disclaimer : This contain an error. See edited post above to have the correction /!\

                JeromeFr wrote:

                 

                Hi,

                You can try with something like this :
                SELECT proj.project_name, folder.folder_name, int.POP_NAME interface_name, km.trt_name ikm, opt.ue_name option_name, int_opt.short_value, int_opt.i_txt_value   FROM DEV_ODI_REPO.SNP_PROJECT proj, DEV_ODI_REPO.SNP_FOLDER folder, DEV_ODI_REPO.SNP_POP int, DEV_ODI_REPO.snp_trt km, DEV_ODI_REPO.snp_user_exit opt, DEV_ODI_REPO.snp_ue_used int_opt   where proj.i_project = folder.i_project     and folder.i_folder = int.i_folder     and km.i_trt = int.i_trt_kim     and km.i_trt = opt.i_trt     and int.i_pop = int_opt.i_instance     and opt.ue_name = 'FLOW_CONTROL'   ORDER BY km.trt_name;
                This will display the project, folder, the interface name, the IKM used, the option and its values (boolean or text).

                As you can see, I made the join on the ikm (km.i_trt = int.i_trt_kim). You might need one more table if you want to do it on LKMs.


                Hope it helps.

                Regards,
                JeromeFr