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