group by problem on view converted from sybase

From: CME <support_at_cmesys.demon.co.uk>
Date: Wed, 15 Aug 2001 15:26:03 +0100
Message-ID: <997885561.27722.0.nnrp-10.c2de4217@news.demon.co.uk>

I have been converting a sybase database to oracle and I have managed to convert all of my views except for ones like this:

This is my original sybase view:

---

alter view

dba.v_TestProductEstimate(TotalNumber,TestID,Agency,ExhibitQuantity,ProductI D,ProductNumber)
  as select(select count(distinct B.id) from dba.v_int_TestProductEstimate as B where

      B.TestID = v_int_TestProductEstimate.TestID and b.Agency = v_int_TestProductEstimate.Agency and

      b.ExhibitQuantity = v_int_TestProductEstimate.ExhibitQuantity),     TestID,Agency,ExhibitQuantity,ProductID,sum(ProductQuantity)   from
    dba.v_int_TestProductEstimate
  group by TestID,Agency,ExhibitQuantity,ProductID

---

and below is the oracle version I am working on:

---

create or replace view

cme.v_TestProductEstimate(TotalNumber,TestID,Agency,ExhibitQuantity,ProductI D,ProductNumber)
  as select(select count(distinct B.id) from cme.v_int_TestProductEstimate B where

      B.TestID = v_int_TestProductEstimate.TestID and b.Agency = v_int_TestProductEstimate.Agency and

      b.ExhibitQuantity = v_int_TestProductEstimate.ExhibitQuantity),     TestID,Agency,ExhibitQuantity,ProductID,sum(ProductQuantity)   from
    cme.v_int_TestProductEstimate
  group by TestID,Agency,ExhibitQuantity,ProductID ;

---

However my oracle version does not work as I get an error saying 'count(distinct B.id) is not a group by expression' any help in how I could make this work would be most welcome. Received on Wed Aug 15 2001 - 09:26:03 CDT