Solved

Query formation

Posted on 2012-04-11
3
432 Views
Last Modified: 2012-04-14
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
Comment
Question by:neoarwin
  • 2
3 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 37836203
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
 
LVL 42

Expert Comment

by:dqmq
ID: 37837792
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
 

Author Closing Comment

by:neoarwin
ID: 37846209
Thanks
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to free up undo space? 3 50
Use of Exception to end a Loop 3 45
Oracle DB monitor SW 21 60
Oracle 12c Default Isolation Level 17 41
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question