1 2 Previous Next 16 Replies Latest reply on Apr 5, 2013 3:02 AM by user648773 Go to original post
      • 15. Re: Need help in query rewrite
        user590978
        Finally with the help of you guru's i rewrite the below query and working fine as expected...

        Thanks
        SELECT COUNT(1)
                  FROM CCTR_INQUIRIES CI, CCTR_CONTACTS CCC,CCTR_ACTIONS CA,CC_USER_DEFINED_CODES CCUDC
                 WHERE CI.INQUIRY_ID=CA.INQUIRY_ID 
                                       AND CA.ACTION_ID =
                                             (SELECT /*+ PUSH_SUBQ */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 CI.CONTACT_ID = CCC.CONTACT_ID
                   AND (TRUNC(SYSDATE) - TRUNC(CI.START_INQUIRY)) >= 60
                   AND CI.ID_TYPE = :PDEPARTMENT
                   AND BCBSM_SERVICING_REPORTS.GET_CSRUSER_QA(CI.CURRENT_QUEUE) = :PCSR_USERID
                   AND CI.CATEGORY_ID=CCUDC.CODE_ID
                   AND CCUDC.CODE_TYPE = 'RTCAT'
        • 16. Re: Need help in query rewrite
          user648773
          SELECT COUNT(1)
          FROM CCTR_INQUIRIES CI,
                    CCTR_CONTACTS CCC,
                    (SELECT DISTINCT CA.INQUIRY_ID
          FROM CCTR_ACTIONS CA,
                         (SELECT CA2.INQUIRY_ID, MAX(CA2.ACTION_ID) ACTION_ID
          FROM CCTR_ACTIONS CA2
          WHERE TRUNC(CA2.ACTION_START_DATE) = &PDATE
                         group by CA2.INQUIRY_ID) ca2
          WHERE CA.STATUS 'Z'
                         and CA.ACTION_ID = ca2.ACTION_ID
                         andd ca.INQUIRY_ID = ca2.INQUIRY_ID) aux
          WHERE INQUIRY_ID = aux.CA.INQUIRY_ID
          AND CI.CONTACT_ID = CCC.CONTACT_ID
          AND (TRUNC(SYSDATE) - TRUNC(CI.START_INQUIRY)) >= 60
          AND CI.ID_TYPE = &PDEPARTMENT
          AND GET_CSRUSER_QA(CI.CURRENT_QUEUE) = &PCSR_USERID
          AND CI.CATEGORY_ID IN
          (SELECT CODE_ID
          FROM CC_USER_DEFINED_CODES
          WHERE CODE_TYPE = 'RTCAT');
          1 2 Previous Next