We have a query which is used as a command object in Crystal reports. This report is execited against IBM DB2 database. It is taking 40 min to getback the results. Here is the Query. Please suggest.

 

 

SELECT
A1.CARR_NUM ,  A1.CST_CTR_NUM ,A1.CLM_CLOSE_STS_CD ,   A1.CLM_RJCT_STS_CD ,A1.CLM_VOID_STS_CD ,  A1.PMT_MTCH_DT , A1.ASC_TRANS_TYP_CD ,  A1.DRUG_DESC ,               
A1.BATCH_NUM ,   T5.TPA_RJCT_CD ,  A1.DIV_NUM , A1.INVC_NUM ,  A1.SERVICE_DT ,  A1.ACTL_REFILL_NUM ,  A1.CLM_DSPSN_CD ,  A1.INVC_RSBMT_CD , A1.TP_TOTAL_DUE_AMT ,   
A1.INTL_BILG_MTHD_CD , A1.CARR_EOB_1_CD ,  T5.CARR_EOB_CD_DESC ,  A1.NTNL_DRUG_CD ,  A1.PTNT_FRST_NM ,  A1.PTNT_MDDL_INTL ,  A1.PTNT_LST_NM ,  A1.PTNT_BIRTH_DT ,
A1.CRDHLDR_ID_NUM   
FROM
 (SELECT DISTINCT
    T1.CARR_NUM ,  T1.CST_CTR_NUM ,  T1.CLM_CLOSE_STS_CD ,  T1.CLM_RJCT_STS_CD ,   T1.CLM_VOID_STS_CD ,  T3.PMT_MTCH_DT ,   
    T3.ASC_TRANS_TYP_CD ,  T2.DRUG_DESC ,  T4.BATCH_NUM , T1.DIV_NUM ,  T1.INVC_NUM ,  T1 . SERVICE_DT ,  T1.ACTL_REFILL_NUM ,                                         
    T1.CLM_DSPSN_CD ,  T1.INVC_RSBMT_CD ,T1.TP_TOTAL_DUE_AMT ,  T1.INTL_BILG_MTHD_CD ,T3.CARR_EOB_1_CD ,T1.NTNL_DRUG_CD ,  T2.PTNT_FRST_NM ,         T2.PTNT_MDDL_INTL , T2.PTNT_LST_NM ,  T2.PTNT_BIRTH_DT ,  T2.CRDHLDR_ID_NUM 
   FROM  
                PDRD2LO.VINVC_CLM T1                             
               , PDRD2LO.VPMNT_DTL T3                             
               , PDRD2LO.VINVC_CLM_2 T2                           
               , PDRD2LO.VINVC_ITM_PHARM  T4                     
                    WHERE  
  T1.CLM_CLOSE_STS_CD ='OP'                     
              AND  T1.CLM_RJCT_STS_CD ='RJ'                       
              AND  T1.CLM_VOID_STS_CD ='NV'                       
              AND  T3.ASC_TRANS_TYP_CD ='40'                      
              AND  T1.CARR_NUM = T3.CARR_NUM                      
           AND   T1.CST_CTR_NUM = T3.CST_CTR_NUM                  
           AND   T1.DIV_NUM = T3.DIV_NUM                          
           AND   T1.INVC_NUM = T3.INVC_NUM                        
           AND   T1.SERVICE_DT = T3.SERVICE_DT                    
           AND   T1.CLM_CTRL_NUM = T3.CLM_CTRL_NUM                
           AND   T1.ACTL_REFILL_NUM = T3.ACTL_REFILL_NUM          
           AND   T1.INVC_ITM_NUM = T3.INVC_ITM_NUM                
           AND   T1.REFILL_TRANS_NUM = T3.REFILL_TRANS_NUM        
           AND   T1.CARR_NUM = T2.CARR_NUM                        
           AND   T1.CST_CTR_NUM = T2.CST_CTR_NUM                  
           AND   T1.DIV_NUM = T2.DIV_NUM                          
           AND   T1.INVC_NUM = T2.INVC_NUM                        
           AND   T1.SERVICE_DT = T2.SERVICE_DT                  
           AND   T1.ACTL_REFILL_NUM = T2.ACTL_REFILL_NUM        
           AND   T1.CLM_CTRL_NUM = T2.CLM_CTRL_NUM              
           AND   T1.INVC_ITM_NUM = T2.INVC_ITM_NUM              
           AND   T1.REFILL_TRANS_NUM = T2.REFILL_TRANS_NUM      
           AND   T1.INVC_CTRL_NUM = T4.INVC_CTRL_NUM) A1        
LEFT OUTER JOIN  PDRD2LO.VCARR_EOB T5                
            ON    A1.CARR_EOB_1_CD = T5.CARR_EOB_CD             
            AND   A1.CARR_NUM = T5.CARR_NUM                     
            AND   A1.DIV_NUM = T5.DIV_NUM        

 

Thanks!