Bob,

PMFJI,

I agree with John, while I can't speak specifically to a SQL Server example, I am currently implementing an Oracle back-end an am using views for the following:

1. Dynamic user queries - By using SPT against views vs base tables, I can predefine join types/conditions, returned columns, ordering, etc thus simplifying the process of building a SQL statement in code. Applied to the example of a reporting package (whether employed by an 'end-user' or 'seasoned-developer') I can ensure that the joins occur on indexed columns and can also translate values returned in the query. For example:

Consider company table with a company_type column=0 to 4

The following view simplifies things for a report developer -

Create or replace view v_Company as Select company_id, company_name,
Decode(company_type,0,'Purchaser',1,'End User',2,'Tire Kicker',
3,'Wanna-Be',4,'Rolling In $ Not Sense') "company_type" from
Company Order by company_name desc;

Additionally, using the above example, we can ensure the appropriate 'ordered' rows return to the user/developer....

'Select * from company where rownum<20 order by company_name' will not return the 1st 20 rows based on company an ordering of company_name but rather an ordering of the 1st 20 companies based on company_name. An query of 'Select * from v_company where like company_name 'A%' will respect the ordering of the view and then evaluate the rownum expression.

2. Since Oracle uses a LRU (Least Recently Used) algorithm to decide what remains in its Shared Pool, dynamic queries involving different joins, where criteria, etc have to be parsed many times (based on how often they are called), you can however 'pin' things (Functions, Stored Procs, etc) into the Shared Pool to increase performance


These are only a couple of things I'm grappling with in the design/implementation of this system. I am also using 100% SPT for selects (against views only), inserts, and updates (using the Oracle Dynamic SQL capability of the DBMS_SQL package) in the current implemention.

I would certainly 'Entertain' opposing views (ouch)

Mark

>>Hi Peter,
>>
>>OK, let me carefully refactor this. As to the hypothetical architecture, think long and hard about letting a report engine directly access data. At the least, think about defining views in SQL Server for Crystal to use.
>>
>John,
>
>There's one I haven't heard recomended before. Why do you asvise this? It seems that and enduser which could create a report with Crystal, may not be able to, or even have access to, create a SQL View.
>
>BOb