Xref: alice comp.databases.oracle.misc:35563
Path: alice!news-feed.fnsi.net!newspump.monmouth.com!newspeer.monmouth.com!newsfeed.mathworks.com!mvb.saic.com!aries.tucson.saic.com!not-for-mail
From: Sarah Officer <officers@aries.tucson.saic.com>
Newsgroups: comp.databases.oracle.misc
Subject: problem creating view
Date: Wed, 07 Jul 1999 14:23:03 -0700
Organization: SAIC Tucson
Lines: 63
Message-ID: <3783C537.94C88C70@aries.tucson.saic.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: aries.tucson.saic.com 931382425 25123 139.121.3.118 (7 Jul 1999 21:20:25 GMT)
X-Complaints-To: postmaster@aries.tucson.saic.com
NNTP-Posting-Date: 7 Jul 1999 21:20:25 GMT
To: officers@aries.tucson.saic.com
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)

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@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;