Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!uunet!mcsun!hp4nl!orcenl!bengsig
From: bengsig@dk.oracle.com (Bjorn Engsig)
Newsgroups: comp.databases
Subject: Re: joining 3 tables in SQL
Message-ID: <1262@dkunix9.dk.oracle.com>
Date: 21 Feb 91 09:42:26 GMT
References: <AE_V-ldatL@bob.srcc.msu.su>
Reply-To: bengsig@dk.oracle.com (Bjorn Engsig)
Organization: Oracle Denmark
Lines: 37

In <447@cti1.UUCP> cti1!kmeek (Kevin Meek) writes:
|
|We want an output record for each account with the total 
|planned amount and total expended amount.  [i.e. a join of two aggregates
|and a table]
|
|I tried creating a view for each separately and then joining my views
|but you can't use a where when your view has a group by clause.
| 
Article <AE_V-ldatL@bob.srcc.msu.su> by butenko@bob.srcc.msu.su says:
| I think that many SQL servers use smth like "macroprocessing" for views, i.e.
| they substitute the text of your view definition into your query and then try
| to interpret it. So, if you can't do something without views, creating some
| views can't help (just an opinion, maybe it's not right for some SQL servers).
Oracle will actually allow you to do that, e.g.

create view plansum(accno,ps) as select accno,sum(amount)
from planned group by accno

create view expsum (accno,ex) as select accno,sum(amount)
from expenses group by accno

select acc.accno, acc.accname, ... , expsum.ex, plansum.ps
from acc, plansum, expsum
where acc.accno = expsum.accno
and   acc.accno = plansum.accno

In C.J.Date "Selected Writing", there is a very good description of how
deficient SQL actually is.  It would for example be very nice to have a
true concept of 'table expressions' so that I could write

select ...
from ( select ... )
-- 
Bjorn Engsig, ORACLE Corporation, E-mail: bengsig@oracle.com, bengsig@oracle.nl

            "Stepping in others footsteps, doesn't bring you ahead"


Brought to you by Super Global Mega Corp .com