Problem converting sybase view to oracle

From: CME <support_at_cmesys.demon.co.uk>
Date: Mon, 20 Aug 2001 09:13:22 +0100
Message-ID: <998295194.5881.0.nnrp-12.c2de4217@news.demon.co.uk>

I have been converting a sybase database to oracle and I am having problems with this view and others similar.

However my oracle version does not work as I get an error saying 'case when sum(quantity)' is not a group by expression any help in how I could make this work would be most welcome.

THE ORIGINAL SYBASE VIEW:

---

alter view
  dba.v_DeliveredUnivoicedAmount(
    caseID,
    Product,
    Price,
    Quantity,
    LatestDeliveredDate) as
  select caseID,

         product,
         if MAX(price) is null
           then 0
           else MAX(Price)
         endif,
         sum(v_DeliveredProducts.quantity)-
           (select(if sum(quantity) is null
               then 0
               else sum(quantity)
             endif)
           from dba.v_invoicelist
           where v_invoicelist.product = v_DeliveredProducts.product
           and v_invoicelist.caseid = v_deliveredProducts.caseID),
           MAX(datedelivered)

  from
    dba.v_deliveredProducts left outer join     dba.v_productlist on v_DeliveredProducts.product = v_productlist.productid and

    datedelivered >= effectivefrom and(datedelivered <= effectiveto or effectiveto is null)
  group by product,caseID

---

BELOW IS THE ORACLE VERSION I AM WORKING ON:

---

CREATE OR REPLACE VIEW
  cme.v_DeliveredUnivoicedAmount(
    caseID,
    Product,
    Price,
    Quantity,
    LatestDeliveredDate) as
  select caseID,

         product,
         case when MAX(price) is null
           then 0
           else MAX(Price)
         end,
         sum(v_DeliveredProducts.quantity)-
           (select(case when sum(quantity) is null
               then 0
               else sum(quantity)
             end)
           from cme.v_invoicelist
           where v_invoicelist.product = v_DeliveredProducts.product
           and v_invoicelist.caseid = v_deliveredProducts.caseID),
         MAX(datedelivered)

  from
    cme.v_deliveredProducts,
    cme.v_productlist
where

    v_DeliveredProducts.product = v_productlist.productid(+) and     datedelivered >= effectivefrom and
    (datedelivered <= effectiveto or effectiveto is null) group by product,caseID
; Received on Mon Aug 20 2001 - 03:13:22 CDT