Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: group by problem on view converted from sybase
The problem is that you need to group by all non-aggregate columns included
in the select ie you would have to include the (select count(...) in the
group by which Oracle doesn't seem to like. The answer is to do the
summation first in a sub-query:
select ( select count(distinct B.id)
from v_int_TestProductEstimate B where B.TestID = A.TestID and B.Agency = A.Agency and B.ExhibitQuantity = A.ExhibitQuantity ), A.TestID, A.Agency, A.ExhibitQuantity, A.ProductID, A.SumProductQuantity from ( select TestID, Agency, ExhibitQuantity, ProductID, sum(ProductQuantity) SumProductQuantity from v_int_TestProductEstimate group by TestID, Agency, ExhibitQuantity, ProductID
Note: you need to be careful about the impact on performance eg if you query the view by productid, you should ensure a good access path is used.
"CME" <support_at_cmesys.demon.co.uk> wrote in message
news:997885561.27722.0.nnrp-10.c2de4217_at_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 Thu Aug 16 2001 - 02:01:53 CDT