Path: utzoo!attcan!utgpu!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!bloom-beacon!oberon!nunki.usc.edu!seligson
From: seligson@nunki.usc.edu (Richard Seligson)
Newsgroups: comp.databases
Subject: INGRES oddity (or is it me?)
Summary: views based on views
Message-ID: <2183@nunki.usc.edu>
Date: 18 Dec 88 07:01:02 GMT
Reply-To: seligson@nunki.usc.edu (Richard Seligson)
Organization: University of Southern California, Los Angeles, CA
Lines: 105

Hello,

I am using the interactive SQL interface to Ingres Version 5.0/01.

My database contains the following tables:

Dept (dname, dcode)
Class (classnum, cnum, dcode, instructor, location, mtgtimes)
Enrollment (socsec, classnum)
... and others, but not referenced here.

I want to create a relation of departments, classes, and the number of 
students in each class.  Based on that relation, I want to create a second
relation of departments and total number of students in its classes.

When I create a table, and then a second table based on the first, I get
the correct results.  However, when I create a view and then create a 
second view based upon the first view, the second view contains incorrect data
(the first view is correct).

I am wondering if it is incorrect to build a relation based on a view (Ingres
doesn't flag any error), or if I've done something wrong.

The SQL statements are:

create table t1 as
select dept.dname, class.classnum, students=count(enrollment.socsec)
from dept, enrollment, class
where class.dcode = dept.dcode
and   enrollment.classnum = class.classnum
group by dept.dname, class.classnum;

create table t2 as
select dname, tot_students=sum(students)
from t1
group by dname;

The (almost) identical statements to create the views are:

create view v1 as
select dept.dname, class.classnum, students=count(enrollment.socsec)
from dept, enrollment, class
where class.dcode = dept.dcode
and   enrollment.classnum = class.classnum
group by dept.dname, class.classnum;

create view v2 as
select dname, tot_students=sum(students)
from v1
group by dname;


The relations created are:

t1 & v1 (they are identical):
|dname               |classnum     |students     |
|------------------------------------------------|
|Comparative Literatu|        92882|            2|
|Comparative Literatu|        92886|            1|
|Computer Science    |        11929|            2|
|Computer Science    |        11932|            1|
|Computer Science    |        11934|            2|
|Computer Science    |        11936|            1|
|Computer Science    |        11938|            1|
|Economics           |        34633|            1|
|Economics           |        73652|            1|
|Economics           |        89234|            3|
|Economics           |        93825|            1|
|Philosophy          |        92822|            3|
|Philosophy          |        92824|            1|
|Philosophy          |        92826|            2|
|Philosophy          |        92830|            1|
|Physical Education  |        74397|            2|
|Physical Education  |        74401|            1|
|Physical Education  |        74403|            1|
|------------------------------------------------|

t2:
|dname               |tot_students |  <-- These are correct
|----------------------------------|
|Comparative Literatu|            3|
|Computer Science    |            7|
|Economics           |            6|
|Philosophy          |            7|
|Physical Education  |            4|
|----------------------------------|

v2:
|dname               |tot_students |
|----------------------------------|
|Comparative Literatu|            3|
|Computer Science    |            3| <-- tot_students is incorrect
|Economics           |            4| <-- tot_students is incorrect
|Philosophy          |            6| <-- tot_students is incorrect
|Physical Education  |            3| <-- tot_students is incorrect
|----------------------------------|


Thanks for your help.  If you respond to me via Email, I will post a
summary.

Rich Seligson    seligson@oberon.usc.edu  - InterNet
                 ...uunet!oberon!seligson - UUCP

p.s.  I may not be able to respond until after Jan. 8th.  I'll
      be away for some of winter break - thanks again!