Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

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

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
sakthikumar
Asked:
sakthikumar
  • 3
1 Solution
 
johnsoneSenior Oracle DBACommented:
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
 
LaskoKCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
MikeOM_DBACommented:
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
 
johnsoneSenior Oracle DBACommented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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