group by problem on converted view

From: CME <support_at_cmesys.demon.co.uk>
Date: Tue, 28 Aug 2001 10:54:39 +0100
Message-ID: <998992453.26804.0.nnrp-10.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 with my group by expressions
any help in how I could make this work would be most welcome.

THE ORIGINAL SYBASE VIEW:

---

create view
  dba.v_TestCalculatedCosts(TestID,Quantity,CostPerExhibit) as     select socimstest.id,

           sum(tblItem.quantity),
           (select Estimate
              from dba.v_testestimate
              where
                (socimstest.laboratory = v_testestimate.laboratoryID or
                v_testestimate.agencyID = socimstest.laboratory) and
                socimstest.test = v_testestimate.testid and
                Range = (select MAX(Range)
                           from dba.v_testestimate
                           where
                             (socimstest.laboratory =
v_testestimate.laboratoryID or
                             v_testestimate.agencyID =
socimstest.laboratory) and
                             socimstest.test = v_testestimate.testid and
                             Range <= sum(tblItem.Quantity)
                        )
            )

    from
    dba.socimstest join dba.testxexhibit on socimstest.id = testxexhibit.testid join

    dba.tblItem on testxexhibit.exhibitid = tblItem.Itemid     group by socimstest.id,socimstest.laboratory,socimstest.test

---

BELOW IS THE ORACLE VERSION I AM WORKING ON:

---

create or replace view
  cme.v_TestCalculatedCosts(TestID,Quantity,CostPerExhibit) as     select socimstest.id,

           sum(tblItem.quantity),
           (select Estimate
              from cme.v_testestimate
              where
                (socimstest.laboratory = v_testestimate.laboratoryID or
                v_testestimate.agencyID = socimstest.laboratory) and
                socimstest.test = v_testestimate.testid and
                Range = (select MAX(Range)
                            from cme.v_testestimate
                            where
                              (socimstest.laboratory =
v_testestimate.laboratoryID or
                              v_testestimate.agencyID =
socimstest.laboratory) and
                              socimstest.test = v_testestimate.testid and
                              Range <= sum(tblItem.Quantity)
                        )
           )

from

    cme.socimstest,cme.testxexhibit,cme.tblItem where

    socimstest.id = testxexhibit.testid and     testxexhibit.exhibitid = tblItem.Itemid group by socimstest.id,socimstest.laboratory,socimstest.test ;

---

thanks for any help Received on Tue Aug 28 2001 - 04:54:39 CDT