Solved

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

Posted on 2011-02-14
1
396 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 73

Accepted Solution

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

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

Suggested Solutions

Title # Comments Views Activity
Oracle RMAN Database Restore 5 52
PL/SQL LOOP CURSOR 3 59
Oracle 12c patching 1 64
report returning null 21 82
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
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.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now