Xref: alice comp.databases.oracle.misc:35564
Path: alice!news-feed.fnsi.net!news.idt.net!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: Re: problem creating view
Date: Wed, 07 Jul 1999 14:36:32 -0700
Organization: SAIC Tucson
Lines: 72
Message-ID: <3783C860.CF3584A0@aries.tucson.saic.com>
References: <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 931383233 25123 139.121.3.118 (7 Jul 1999 21:33:53 GMT)
X-Complaints-To: postmaster@aries.tucson.saic.com
NNTP-Posting-Date: 7 Jul 1999 21:33:53 GMT
X-Mailer: Mozilla 4.07C-SGI [en] (X11; I; IRIX 6.5 IP32)

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