View vs. Tables in CR

[fiat2]

I m trying to create a report.
For Crystal version 8.5.

What are the differences of using a database view vs. database tables?

Thanks

 

[synapsevampire]

To Crystal there isn't much difference, why do you ask?

A Vierw is just SQL (unless you mean a Materialized View), and a table contains data.

In order to use Views in Crystal you need to turn on the File->Options->Database->and select Views in the data explorer area

-k

 

[kskid]

A view is basically a consolidation of several tables to represent a "view" of a business process.

However, before using a view in your report, you might want to check out not only the tables involved but also the selection criteria used to generate that view. If it meets your criteria, then by all means, use the view.

Otherwise, you will have to create the report and specify the tables you need along with the links and record selection.

-LW

 

[synapsevampire]

LW: A View is NOT a consolidation of several tables, it is SQL. It does not contain any data (unless as I previously mentioned, you're speaking of Materialized Views).

It can be for one table, or even just static values which used NO tables, or for 100+ tables (depending upon the database and what it supports), or it may be based on other Views, or it might use the same table many times, etc.

It is a SQL statement, albeit limited in it's scope from what a Storeed Prodcedure offers.

-k

 

[kskid]

K,

You are absolutely right but from a Crystal standpoint, I just treat it as a single table. Why duplicate the work if the DBA already created the view you need to create your report.

-LW

 

[synapsevampire]

I'm the dba

I just fear oversimplifications can lead to misundertandings, thinking of it as SQL should get the same point across.

-k

 

[fiat2]

It appears that why question sparked a wonderful debate.


synapsevampire- the reason I ask this question is that I was wondering about report performance but I'm ok with the response.

thank you

 

[dhulbert]

Anothe reason for using views rather than tables is that you can build the business rules in the view and ensure some better consistency in wht is reported, also I believe that using views shifts the processing to the SQL server box.

 

[synapsevampire]

dhulbert: the processing is on the server for the select and from portion anyway, regardless of whether it's on SQL Server or another database. At issue is your record selection criteria, which should be transposed to the SQLs where clause.

How it is optimized is based on many factors, but what we want is for all of the filtering criteria to be passed to the database.

To elaborate slightly, many will switch to Stored Procedures (another form of database object) with the intent of enjoying significant performance improvements, which isn't always the case.

fiat2: A View can perform the same as passing SQL with the filtering criteria, but in some instances you'll generate better SQL with the GUI, the key is to test.

-k

 

[OrionStar]

I always use VIEWS by preference over tables. Although, I must say that you will always need to go away from views as there will invariably be times when they will not provide the information you need.

In my experience, the VIEW is basically a way that the tables have been masterfully linked for efficiency in processing. This is much easier that trying to look at two or more tables and trying to correctly link them together. Especially if the tables are new to YOU>

M

"A man is only as happy,... as he makes up his mind to be...

 

[MyComputerGuy]

In response to the original question, in laymens terms, A View is a process which shows the user only the data that they need or want to see, not all of the data available. Thus, some of the work has already been done for you, i.e. linking the tables and selecting which fields are available for use.