problem creating view

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:23:03 CDT