Re: problem creating view

Would you happen to be getting a "ORA-00979: not a GROUP BY expression"? If so, make sure all columns in your SELECT clause that aren't in an aggregate function(MAX, MIN, COUNT, SUM, etc.) are in the GROUP BY clause. In your sample create view statement, the f.id column is missing from the GROUP BY clause. If this isn't the error message, then a posting of the exact error message would help...hope this helps, though.

Jeff S

Sarah Officer <officers_at_aries.tucson.saic.com> wrote in message news:3783C860.CF3584A0_at_aries.tucson.saic.com...
> Oops. I lied. The listed error message is not the one I'm getting.
> Can anyone help me create the view I want?
>
> Thanks,
>
> Sarah
> officers_at_aries.tucson.saic.com
>
> Sarah Officer wrote:
> >
> > I am trying to create a view that joins two tables. I can execute
> > the query part of the view, and data is returned, but when I try to
> > create the view I get the message
> >
> > ORA-00942: table or view does not exist
> >
> > Here's what it looks like
> >
> > create table foo(
> > id varchar2(10) PRIMARY KEY,
> > idate date NOT NULL
> > );
> >
> > create table foo_category(
> > id varchar2(10) NOT NULL,
> > category varchar2(10) NOT NULL
> > );
> >
> > (id + category are the unique key)
> > I want to create a view which has id, idate, and category, but only
> > one
> > entry for each category. The entry should be the id & idate for the
> > latest date.
> >
> > Here's some data and what I tried. Can anyone help?
> >
> > Thanks,
> >
> > Sarah
> > officers_at_aries.tucson.saic.com
> >
> > insert into foo(id, idate)
> > values('id1',TO_DATE('02/20/1998 13:40:47','MM/DD/YYYY
> > HH24:MI:SS'));
> >
> > insert into foo(id, idate)
> > values('id2',TO_DATE('02/21/1998 13:40:47','MM/DD/YYYY
> > HH24:MI:SS'));
> >
> > insert into foo(id, idate)
> > values('id3',TO_DATE('02/22/1998 13:40:47','MM/DD/YYYY
> > HH24:MI:SS'));
> >
> > insert into foo_category(id, category)
> > values('id1', 'A');
> > insert into foo_category(id, category)
> > values('id2', 'A');
> >
> > insert into foo_category(id, category)
> > values('id3', 'B');
> > insert into foo_category(id, category)
> > values('id2', 'B');
> >
> > select f.id, f.idate, fc.category
> > from foo f, foo_category fc
> > where f.id = fc.id;
> >
> > create view latestfoo as
> > select max(f.idate), f.id, fc.category
> > from foo f, foo_category fc
> > where f.id = fc.id
> > group by fc.category;
Received on Wed Jul 07 1999 - 17:17:52 CDT