Re: problem creating view

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 - 16:36:32 CDT