This discussion is archived
4 Replies Latest reply: Sep 16, 2013 4:37 PM by JeromeFr RSS

List all interfaces that use a certain option on the KM

knayam247 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    You're welcome, glad it helped :)
  • 4. Re: List all interfaces that use a certain option on the KM
    JeromeFr Expert
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points