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

x
?
Solved

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

Posted on 2011-02-14
1
Medium Priority
?
404 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month10 days, 19 hours left to enroll

571 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