Improve company productivity with a Business Account.Sign Up

x
?
Solved

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

Posted on 2011-02-14
1
Medium Priority
?
406 Views
Last Modified: 2013-12-19
 BELOW WORKS CURRENTLY, BUT FAILS when I UNCOMMENT
  the below "and..." line that is right above the "GROUP BY".
 
  How can I fix so it only shows if COMBOQTY - COMBOOPEN > 0 ?
 
  select POLA.ORDER_NO || '_' || POLA.LINE_NO || '_' || POLA.RELEASE_NO COMBO,
  (SUM(POLA.BUY_QTY_DUE)) COMBOQTY,
  (select SUM(QTY_ARRIVED) from PURCHASE_RECEIPT_TAB where ORDER_NO = POLA.ORDER_NO and ROWSTATE = 'Received') COMBOOPEN
  FROM PURCHASE_ORDER_LINE_TAB POLA
  INNER JOIN purchase_order_TAB po
  ON PO.ORDER_NO = POLA.ORDER_NO
  where POLA.WANTED_DELIVERY_DATE < sysdate - 7
  AND POLA.ROWSTATE NOT IN ('Planned','Cancelled')
  and PO.ROWSTATE not in ('Planned','Cancelled')
  and PO.ORDER_NO = 'M3'
  --and ((SUM(POLA.BUY_QTY_DUE)) - (select SUM(PRT.QTY_ARRIVED) from PURCHASE_RECEIPT_TAB PRT where PRT.ORDER_NO = POLA.ORDER_NO and PRT.ROWSTATE = 'Received') > 0)
  group by POLA.ORDER_NO || '_' || POLA.LINE_NO || '_' || POLA.RELEASE_NO, POLA.ORDER_NO;
0
Comment
Question by:finance_teacher
1 Comment
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 34888612
you can reference the aggregate results in the where clause of the query that generates those aggregates
a HAVING clause may be more appropriate here
SELECT   pola.order_no || '_' || pola.line_no || '_' || pola.release_no combo,
         (SUM(pola.buy_qty_due)) comboqty,
         (SELECT SUM(qty_arrived)
            FROM purchase_receipt_tab
           WHERE order_no = pola.order_no AND rowstate = 'Received')
             comboopen
    FROM purchase_order_line_tab pola INNER JOIN purchase_order_tab po ON po.order_no = pola.order_no
   WHERE     pola.wanted_delivery_date < SYSDATE - 7
         AND pola.rowstate NOT IN ('Planned', 'Cancelled')
         AND po.rowstate NOT IN ('Planned', 'Cancelled')
         AND po.order_no = 'M3'
GROUP BY pola.order_no || '_' || pola.line_no || '_' || pola.release_no, pola.order_no
  HAVING ((SUM(pola.buy_qty_due))
          - (SELECT SUM(prt.qty_arrived)
               FROM purchase_receipt_tab prt
              WHERE prt.order_no = pola.order_no AND prt.rowstate = 'Received') > 0)

Open in new window

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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.

606 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