Views on Views

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 Received on Wed Oct 16 2002 - 10:44:14 CDT