Re: group by problem on view converted from sybase

On Wed, 15 Aug 2001, support_at_cmesys.demon.co.uk wrote:

> 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

>;


> --------------------------------------------------------

Oracle is looking for the columns in your group by list to be part of your select clause.

I don't create views in Oracle by referencing their output columns in the parentheses. Just execute SQL that you need to be retrieved and then slap a "create view blah as" on top of it.

-- 
Galen Boyer
It seems to me, I remember every single thing I know.