This discussion is archived
3 Replies Latest reply: Apr 4, 2013 3:33 PM by 651776 RSS

Rewrite the query for better performance.

user590978 Newbie
Currently Being Moderated
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;

Legend

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