view on a synonym

IDS 9.21.UC4 If a view is created on a synonym, then the view definition takes the base table and not the synonym table. For e.g. 'table_a' has a synonym 'table'. new if a view is created as follows create view table_viewas select * from table ; the definition of the view is recorded as create view table_viewas select * from table_a ; For us this is creating two problems. 1. our scripts to compare schemas is reporting a diff on this. 2. we can not guarantee that table_a will exist at all times. We have two sets of same table table_a and table_b with a synonym pointing to one of them. The idea behind this is to keep the application online when all users use table_b via synonym, while we do data loading/modification to table_a. For that we even do drop and load the table and finally a switch in synonymn. Since the view definition is static to base table, we have to change the view definition also every time. An unnecessary step for us. Is this a bug or an expected behavior with views. ravi

Synonyms are used to provide an alternate name for an object. Views can be used to do the same thing. One of the subtitle differences between a synonym and a view is that the view is realized by the optimizer while the synonym is realized by the parser. You can achieve what you want by using a view to define the base table instead of a synonym and then define a view on top of the base view. The higher view will be referencing the lower view, not the base table. "rkusenet" <rkusenet@sympatico.ca> wrote in message news:beem85$4agno$1@ID-75254.news.dfncis.de... > IDS 9.21.UC4 > > If a view is created on a synonym, then the view definition > takes the base table and not the synonym table. > > For e.g. > > 'table_a' has a synonym 'table'. > > > new if a view is created as follows > > create view table_view> as select * from table ; > > the definition of the view is recorded as > create view table_view> as select * from table_a ; > > For us this is creating two problems. > > 1. our scripts to compare schemas is reporting a diff on this. > > 2. we can not guarantee that table_a will exist at all times. > We have two sets of same table table_a and table_b with > a synonym pointing to one of them. The idea behind this is to > keep the application online when all users use table_b via > synonym, while we do data loading/modification to table_a. > For that we even do drop and load the table and finally > a switch in synonymn. > > Since the view definition is static to base table, we have to change > the view definition also every time. An unnecessary step for us. > > Is this a bug or an expected behavior with views. > > ravi > > > >