Just a warning on doing this:

We had a similar situation where we had a view that made a list of
employees with a specific setting. We then made a view for a report we
were writing that used that first view.

The need then arose to add an extra condition to the where clause of
the first view. As there is no way to "alter view", you have to drop
and create the view, but, and here is where it gets messy, you are not
allowed to drop the view without first dropping every stored
procedure, trigger, and other view that relies on it, and of course
those views can't be dropped unless nothing relies on them.

To add one line of code to the first view, we would have had to drop
about 15 views, and temporarily alter about 5 stored procedures and
triggers.

You may want to consider using a stored procedure in place of the
view, and that way you can always alter it later without this sort of
nonesense.

Adam


--- In firebird-support@yahoogroups.com, "norgepaul"
<paul.thornton@c...> wrote:

>
> Hi,
>
> Could somebody please tell me if the following is possible:
>
> I have two tables
>
> TABLE_A:
> =========================
> AKEY
> 1
> 2
> 3
> 4
> =========================
>
>
> TABLE_B:
> =========================
> BKEY AKEY VALUE
> 1 1 Val1
> 2 2 Val2
> 3 3 Val3
> 4 4 Val4
> 5 4 Val5
> 6 4 Val6
> =========================
> What I would like to do is this. Create a view on Table A that limits
> the records that can be seen by a user:
>
> CREATE VIEW MYVIEW AS SELECT * FROM TABLE_A WHERE AKEY=1 OR AKEY=4
>
> I would then like to create a View on B that will only allow the user
> to see the fields that are linked to Table A (via "AKEY") and that are
> visible in "MYVIEW"
>
> I was thinking along the lines of:
>
> CREATE VIEW "LIMITED_VIEW" AS SELECT VALUE FROM TABLE_A TABA WHERE
> (EXISTS (SELECT AKEY FROM MYVIEW TABB WHERE TABA.AKEY = TABB.BKEY))
>
> The result should be:
> "LIMITED_VIEW"
> =========================
> AKEY BKEY VALUE
> 1 1 Val1
> 4 4 Val4
> 5 4 Val5
> 6 4 Val6
> =========================
>
>
> Unfortunately, this does not produce any records.
>
> The idea is that as TABLE_A changes, the records that "LIMITED_VIEW"
> can see also changes.
>
> Does anybody understand? ;o) Is what I am asking possible? If so, how?
>
> Cheers,
> Paul
>
> P.S. Thanks to Helen for a superb book :o)