3 Replies Latest reply: Apr 4, 2013 5:33 PM by user648773 RSS

    Rewrite the query for better performance.

    user590978
      Hi,
      First of all , Thanks to everyone here..

      Ca we rewrite the query..
       WITH T AS
        (
         SELECT Bcbsm_Servicing_Reports.GET_PRODUCT_BYGROUP(Bcbsm_Servicing_Reports.GET_MEMBER_GROUP(Bcbsm_Servicing_Reports.GET_SUBSCRIBER_ID(CI.CONTACT_ID,
                                                                                                                                               CI.INQUIRY_ID))) "PRODUCT" -- INTO V_PENDING_CASES
                  ,ci.id_type,inquiry_id,ci.current_queue FROM CCTR_INQUIRIES CI
                   WHERE CI.CATEGORY_ID IN
                         (SELECT CODE_ID
                            FROM CC_USER_DEFINED_CODES
                           WHERE CODE_TYPE = 'RTCAT')
                   AND ci.id_type = PDEPARTMENT
      
      
         )
         SELECT COUNT(*) 
          FROM t
         WHERE inquiry_id IN  (SELECT DISTINCT CA.INQUIRY_ID
                            FROM CCTR_ACTIONS CA
                           WHERE CA.ACTION_ID IN
                                 (SELECT MAX(CA2.ACTION_ID)
                                    FROM CCTR_ACTIONS CA2
                                   WHERE TRUNC(CA2.ACTION_START_DATE) = &PDATE
                                     AND CA2.INQUIRY_ID = CA.INQUIRY_ID)
                             AND CA.STATUS  <>'Z'
                             )
         AND Bcbsm_Servicing_Reports.GET_CSRUSER_QA(t.CURRENT_QUEUE) = &PCSRUSER_ID
         AND t.product = &PPRODUCT;