3 Replies Latest reply on Jun 6, 2016 11:41 AM by Bashar.

    10 top xml concurrent programs

    happy10319

      Hi,

      on 11.2.5.10 on AIX DB 11.2.0.3

      I'm looking for a query which returns 10 top XML  concurrent programs we run.

       

      I thought about this (but not sure) :

       

      select cp.concurrent_program_name, count(*) CNT from
             applsys.fnd_concurrent_requests cr,
                 applsys.fnd_concurrent_programs cp,
                 applsys.fnd_concurrent_programs_tl cpt,
                 applsys.fnd_user usr

      where   CR.program_application_id = CP.APPLICATION_ID
      and      CR.CONCURRENT_PROGRAM_ID =   CP.concurrent_program_id
      and      CR.PROGRAM_APPLICATION_ID = cpt.application_id +
      and      CR.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID +
      and      'F' = CPT.LANGUAGE
      and      usr.user_id = CR.REQUESTED_BY


      and cr.output_file_type = 'XML'
      group by cp.concurrent_program_name order by CNT DESC

       

       

      Thanks.

        • 1. Re: 10 top xml concurrent programs
          Bashar.

          Hi,

           

          The query is correct but it returns all programs, not just the top 10.

          You should remove the outer join as it is not needed.

           

          Regards,

          Bashar

          • 2. Re: 10 top xml concurrent programs
            happy10319

            Thank you.

            Outer join ?

            Would you be kind to tell me which one of these :

            where   CR.program_application_id = CP.APPLICATION_ID

            and      CR.CONCURRENT_PROGRAM_ID =   CP.concurrent_program_id

            and      CR.PROGRAM_APPLICATION_ID = cpt.application_id +

            and      CR.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID +

            and      'F' = CPT.LANGUAGE

            and      usr.user_id = CR.REQUESTED_BY

             

            Or shuold I only  choos the first 10. Like this :

            select * from (

            select cp.concurrent_program_name, count(*) CNT from
                   applsys.fnd_concurrent_requests cr,
                       applsys.fnd_concurrent_programs cp,
                       applsys.fnd_concurrent_programs_tl cpt,
                       applsys.fnd_user usr

            where   CR.program_application_id = CP.APPLICATION_ID
            and      CR.CONCURRENT_PROGRAM_ID =   CP.concurrent_program_id
            and      CR.PROGRAM_APPLICATION_ID = cpt.application_id +
            and      CR.CONCURRENT_PROGRAM_ID = CPT.CONCURRENT_PROGRAM_ID +
            and      'F' = CPT.LANGUAGE
            and      usr.user_id = CR.REQUESTED_BY


            and cr.output_file_type = 'XML'
            group by cp.concurrent_program_name order by CNT DESC
            ) where rownum < 11

             

             

            Regards.

            • 3. Re: 10 top xml concurrent programs
              Bashar.

              The plus sign + indicates that you might be using an outer join.

              The second query returns the first 10 rows, which is what you were looking for.

               

              Regards,

              Bashar