?
Solved

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

Posted on 2011-02-14
1
Medium Priority
?
402 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
[X]
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
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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

770 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