modify view content

[j***@gmail.com]

I got a problem with modifing view contents
a simple example:
create table t1(c1 int,c2 int);
create view v1 as select c1 from t1;
create view v2 as select * from v1;
create view v3 as select * from v2;

if I modify v1 as
create view v1 as select c2 from t1;

I have to drop view v1, then recreate the view v1.
But It will delete all the views(v2 & v3) refer to the view v1.

How can I avoid this ?

[Mike Walker]

Yes, this can sometimes be a pain.

When you drop a view or table, any views built on that view or table will
also be dropped.

You can use the sysdepend table to identify the views that are dependent on
other tables and views, so you know what will need to be recreated following
the drop. In your example, you would need to run the query recursively -
first to identify that v2 is dependent on v1, and again to identify that v3
is dependent on v2.

Mike


-----Original Message-----
From: informix-list-***@iiug.org [mailto:informix-list-***@iiug.org]
On Behalf Of ***@gmail.com
Sent: Monday, March 10, 2014 11:03 PM
To: informix-***@iiug.org
Subject: modify view content

I got a problem with modifing view contents a simple example:
create table t1(c1 int,c2 int);
create view v1 as select c1 from t1;
create view v2 as select * from v1;
create view v3 as select * from v2;

if I modify v1 as
create view v1 as select c2 from t1;

I have to drop view v1, then recreate the view v1.
But It will delete all the views(v2 & v3) refer to the view v1.

How can I avoid this ?