Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Is there any easier/effective way of writing this query.

Posted on 2014-10-30
Medium Priority
Last Modified: 2015-01-05
Below is my query,
I just want to check, is there any other easier/effective query.

              FROM MQ_DPC_BOES MDB
              JOIN MQ_DPC_PKGS MDP
                   NVL(MDP.DEL_IND, 'N') = 'N')
               AND MDB.BILL_NO = lv_Boe_No
               AND REC_TYPE = 'I') +
           (SELECT NVL(SUM(MDG.QTY), 0)
              FROM MQ_DPC_BOES MDB
              JOIN MQ_DPC_GOODS MDG
               AND MDB.BILL_NO = lv_Boe_No
               AND REC_TYPE = 'I') INTO LV_QTY
      FROM DUAL;

with mq_dpc_boes i have to join mq_dpc_pkgs and mq_dpc_goods.
a record in mq_dpc_boes will either present in mq_dpc_pkgs or mq_dpc_goods.
Question by:sakthikumar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
LVL 35

Accepted Solution

johnsone earned 2000 total points
ID: 40412967
I'm not sure I completely understand your requirement.  If I do, then this should work.

SELECT Nvl(SUM(MDP.tot_no_pkgs), 0) 
       + Nvl(SUM(MDG.qty), 0) 
FROM   mq_dpc_boes MDB 
       left outer join mq_dpc_pkgs MDP 
                    ON ( MDP.doc_no = MDB.doc_no 
                         AND MDP.bill_no = MDB.bill_no 
                         AND Nvl(MDP.del_ind, 'N') = 'N' ) 
       left outer join mq_dpc_goods MDG 
                    ON ( MDG.doc_no = MDG.doc_no 
                         AND MDG.bill_no = MDB.bill_no ) 
WHERE  MDB.bill_no = lv_boe_no 
       AND MDB.rec_type = 'I' 

Open in new window


Expert Comment

ID: 40413038
you can join mq_dpc_boes with union of mq_dpc_pkgs and mq_dpc_goods. something like this:
select sum(u.qty)
from mq_dpc_boes b,
  (select DOC_NO, BILL_NO, TOT_NO_PKGS qty
   from mq_dpc_pkgs
   where NVL(DEL_IND, 'N') = 'N' and rec_type = 'I'
   union all
   select DOC_NO, BILL_NO, qty
   from mq_dpc_goods
   where and rec_type = 'I'
  ) u
where b.doc_no = u.doc_no and b.bill_no = u.bill_no
  and b.bill_no = lv_boe_no
LVL 35

Expert Comment

ID: 40413083
I don't see how the UNION ALL of the 2 sub tables is going to be a more efficient solution.  That subquery would have to be materialized before the query could be run and the combination of those 2 tables could have 10s (or even 100s) of millions of rows.  Then you would have to scan that entire set of rows to find a result without an index.
LVL 29

Expert Comment

ID: 40413186
Johnson's solution may work, but I would make a small correction:
SELECT SUM ( NVL ( Mdp.Tot_No_Pkgs, 0 ) ) 
     + SUM ( NVL ( Mdg.Qty, 0 ) )
  FROM Mq_Dpc_Boes Mdb
       LEFT OUTER JOIN Mq_Dpc_Pkgs Mdp
          ON ( Mdp.Doc_No = Mdb.Doc_No
           AND Mdp.Bill_No = Mdb.Bill_No
           AND NVL ( Mdp.Del_Ind, 'N' ) = 'N' )
       LEFT OUTER JOIN Mq_Dpc_Goods Mdg
          ON ( Mdg.Doc_No = Mdg.Doc_No
           AND Mdg.Bill_No = Mdb.Bill_No )
 WHERE Mdb.Bill_No = Lv_Boe_No
   AND Mdb.Rec_Type = 'I';

Open in new window

LVL 35

Expert Comment

ID: 40413241
Good catch.  I just copied the original on the sum and nvl.  If there is only 1 record from the child tables, then it doesn't matter, but if one is null and the others are not, the original would probably give incorrect results.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

730 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