Re: Views on Views

Rey wrote:

> Howdy all.
>
> I'm coming from a desktop database environ (Paradox, Access). In our Paradox
> app, we create several interim tables in order to get our final report or
> acct info.
> These interim tables are created from queries of the base tables on the file
> server and some of the tables
> contain the results of a calculated/aggregate function, i.e. SUM bal as
> CurBAl.
>
> In the process of converting from file server to client server, I've been
> advised that the interim
> tables can "created" by means of taking the SQL statments and creating
> views. However, these views to not have the filter info, i.e. the user's
> input selections for which he wants the rpt be that association 003,
> 005, 007.
>
> Then after several queries where the interim tables are created, a
> subsequent query in the chain takes the results of the interim tables
> and creates a table which combines the interim tables and may do some
> additional calculation (SUM or curbal - futureexpenses).
>
> From what i've been told/informed, it is at this query where the user's
> selection is added
> to the Where clause. It is also my understanding that the views previous to
> this query (dependancies) will be
> "fired off" thus narrowing/reducing the number of rows which the final query
> acts upon.
>
> My question is: is this correct or is my understanding flawed/missing
> something... I've not yet gotten into
> looking at named cursors to hold the recordset of the interim tables.
> Reviewing Google for posts on views on views, I came
> across one that indicated that the server could slow a query down when
> attempting to resolve dependant queries...
>
> Would also appreciate being directed to what documentation/book, etc I
> should look at that explains this process
> as nothing I've accessed to incl purchasing The Practical SQL Handbook and
> SQL Queries for Mortals even addresses querying multiple
> views which build on themselves.
>
> BTW, am also currently enrolled in the local regional college (Glendale, AZ)
> for a class on PL/SQL.
>
> Thanks in advance for your help and suggestions,
> Rey

To be perfectly honest I couldn't follow what you wrote so from my perspective your understanding is flawed.

You could use views but based on what I think you are trying to do there is no reason to do so.

Look at using what Oracle calls in-line views (others call derived tables or in-memory tables) and do something like this:

SELECT field1, field2, field3
FROM (
   <another select statement here>)
WHERE ... some conditions are met

Substitute the SQL that would have been used to create the view for <another select statement here>.
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c08schem.htm#18122

If this doesn't help ... rather than trying to explain what you think things are ... just ask for help trying to solve a specific question.

Daniel Morgan Received on Wed Oct 16 2002 - 11:02:01 CDT