Link to home
Start Free TrialLog in
Avatar of finance_teacher
finance_teacher

asked on

ORACLE -- SQL "COMBOQTY - COMBOOPEN" ?

 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;
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial