Solved

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

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

       SELECT (SELECT NVL(SUM(TOT_NO_PKGS), 0)
              FROM MQ_DPC_BOES MDB
              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')
               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
                ON (MDG.DOC_NO = MDG.DOC_NO AND MDG.BILL_NO = MDB.BILL_NO)
               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.
0
Comment
Question by:sakthikumar
  • 3
5 Comments
 
LVL 34

Accepted Solution

by:
johnsone earned 500 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

0
 
LVL 1

Expert Comment

by:LaskoK
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
0
 
LVL 34

Expert Comment

by:johnsone
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.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
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

:p
0
 
LVL 34

Expert Comment

by:johnsone
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

823 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