Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

Query formation

Hi,

Is it possible to rewrite the below query any differently.
I think it is little bit redundant and it takes up some extra cost but I don't know how to rewrite this to get..please help.

SELECT distinct a.ord_id cancelled_ord_id, a.last_ord_id AS rebilled_order_id, a.acct_nr, 'TOTAL' as Type    
  FROM sir_invc a, SIR_INVC_CNCLTN_RQST ICR  
WHERE  
 a.ord_id not in (                  
                                 
                 select distinct b.last_ord_id from sir_invc b, SIR_INVC_CNCLTN_RQST ICR
                     where
                   b.last_ord_id is not null
                     and b.bilng_in68_cd = 129
                     and b.wrhs_cd = 37
                     and b.zone_cd = 1                  
                        AND ICR.ACCOUNT_NUMBER =  b.acct_nr
                   AND ICR.CAMPAIGN_YEAR =   b.cmpgn_yr
                 AND ICR.CAMPAIGN_NUMBER = b.cmpgn_nr
                 AND ICR.ord_id = b.ord_id
                 AND ICR.invc_cncltn_answr_dt is not null
                 AND TO_CHAR(ICR.WORK_DT,'DD-MM-YYYY') = '11-04-2012'      
                 AND b.last_point_trkg_cd = 10      
                   
                     )

and a.last_ord_id is null
and a.last_point_trkg_cd = 10
and a.bilng_in68_cd = 129
and a.wrhs_cd = 37
and a.zone_cd = 1
AND ICR.ACCOUNT_NUMBER =  a.acct_nr
AND ICR.CAMPAIGN_YEAR =   a.cmpgn_yr
AND ICR.CAMPAIGN_NUMBER = a.cmpgn_nr
AND ICR.ord_id = a.ord_id
AND ICR.invc_cncltn_answr_dt is not null
AND TO_CHAR(ICR.WORK_DT,'DD-MM-YYYY') = '11-04-2012'


Best Regards,
Neoarwin
0
neoarwin
Asked:
neoarwin
  • 2
1 Solution
 
dqmqCommented:
SELECT distinct a.ord_id cancelled_ord_id, a.last_ord_id AS rebilled_order_id, a.acct_nr, 'TOTAL' as Type    
  FROM sir_invc a, SIR_INVC_CNCLTN_RQST ICR  
WHERE  
 a.ord_id not in (                  
                                 
                 select b.last_ord_id from sir_invc b, SIR_INVC_CNCLTN_RQST c
                     where
                     and b.bilng_in68_cd = a.bilng_in68_cd
                     and b.wrhs_cd = a.wrhs_cd
                     and b.zone_cd = a.zone_cd
                        AND c.ACCOUNT_NUMBER =  b.acct_nr
                   AND c.CAMPAIGN_YEAR =   b.cmpgn_yr
                 AND c.CAMPAIGN_NUMBER = b.cmpgn_nr
                 AND c.ord_id = b.ord_id
                 AND c.invc_cncltn_answr_dt is not null
                 AND TRUNC(c.WORK_DT) = TRUNC(a.WORK_DT)
                 AND b.last_point_trkg_cd = a.last_point_trkg_cd
                   
                     )

and a.last_ord_id is null
and a.last_point_trkg_cd = 10
and a.bilng_in68_cd = 129
and a.wrhs_cd = 37
and a.zone_cd = 1
AND ICR.ACCOUNT_NUMBER =  a.acct_nr
AND ICR.CAMPAIGN_YEAR =   a.cmpgn_yr
AND ICR.CAMPAIGN_NUMBER = a.cmpgn_nr
AND ICR.ord_id = a.ord_id
AND ICR.invc_cncltn_answr_dt is not null
AND TO_CHAR(ICR.WORK_DT,'DD-MM-YYYY') = '11-04-2012'
0
 
dqmqCommented:
Remove the first "distinct" will also improve performance, but I don't know if it would change the results.    And other performance improvements may be possible, but I cannot discern as much without a data model.
0
 
neoarwinAuthor Commented:
Thanks
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now