Solved

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Loading flat file data in tables 2 65
oracle query 3 26
populate value based on what is selected in lov 2 37
Oracle SQL Developer - SubString 2 31
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…
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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

730 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