Re: group by problem on converted view

"CME" <support_at_cmesys.demon.co.uk> wrote in message news:998992453.26804.0.nnrp-10.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 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
>
>

No version of Oracle, no exact error message, no table definitions. How do you expect help?

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Tue Aug 28 2001 - 05:32:13 CDT