Re: Problem converting sybase view to oracle

"CME" <support_at_cmesys.demon.co.uk> wrote in message news:998295194.5881.0.nnrp-12.c2de4217_at_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
> ;
>
>

case when sum(quantity) is null

               then 0
               else sum(quantity)
             end)

nvl(sum(quantity),0)

Oracle can be easy, can't it?

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Mon Aug 20 2001 - 14:27:10 CDT