On 26 Feb 2002 at 12:15, rogervellacott wrote:

> I stopped using views for the reasons you state, but mainly because it
> makes the whole database much more inflexible during development. Any
> field mentioned in a view cannot be dropped unless you first change
> the view, and every time you add a field, you have to think about
> whether it needs to be added to views, etc. This makes it very
> difficult to use an iterative development process (ie, one where you
> have not planned all your tables and structures in detail in advance).
>

I use views all the time, one reason being Crystal @#!$ Reports, Crystal needs to see
data in a linear manner, in a way that it can access it from ODBC, so the following is
almost impossible, from Crystal, but could be needed:

SELECT P1.PRODUCT_ID, P1.PRODUCT_DESC, P2.PRICE
FROM PRODUCTS P1
JOIN PRICES P2 WHERE P2.PRODUCT_ID = P1.PRODUCT_ID AND
P2.EFFECTIVE_DT = (SELECT MAX(EFFECTIVE_DT) FROM PRICES P3 WHERE
P3.PRODUCT_ID = P1.PRODUCT_ID AND EFFECTIVE_DT <= CURRENT_DATE)

The above extracts the current price, as of today, it will skip older prices and newer
prices, returning the current price. Because Crystal will trip over the second select,
it's simple, turn the whole thing into a view, and then simply use the view.

I don't really get how adding a field to a database, means having to add it to existing
views, unless the software using that view needs that data, it's the beauty of using
SQL, adding new fields, only affects programs and views that need that data. As for
deleting fields, I very rarely delete a field, I would rather document that a field has
been abandoned, and simply ignore it. True this may waste some storage, but in the
days of affordable 100GB hard disks, who really cares? When a system is redone or
replaced, then the abandoned fields get deleted.

Designing the data in advance is always a good idea, actually doing a full design of
the code, before typing one line, is always a good idea, it saves money and time.

Paul

>
>
> --- In ib-support@y..., Carsten Schäfer <ca_schaefer@g...> wrote: >
> Hi, > in general i create views to select from columns of different
> tables with one select. > I was thinking that the view is once created
> and than updated every time when a underlying table is updated. > But
> my tests shows me that there is done a join on the tables everey time
> i make a select on the view. > So there is no difference (in speed)
> when i use an explicit join in my select and not using the view. > >
> So why should somebody use a view ? > > > gruse > Carsten
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~--> Tiny Wireless Camera under $80! Order Now!
> FREE VCR Commander! Click Here - Only 1 Day Left!
> http://us.click.yahoo.com/nuyOHD/7.PDAA/yigFAA/67folB/TM
> ---------------------------------------------------------------------~
> ->
>
> To unsubscribe from this group, send an email to:
> ib-support-unsubscribe@egroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/
>
>
>

Paul Schmidt
Tricat Technologies
paul@...
www.tricattechnologies.com