RE: Problem using Views with Multiple tables

Posted in 2003

Topics: SQL Development & Query Writing, Security, Permissions & Auditing

Murray Wood — Wed, 14 May 2003 08:40:54 +1200 — source: Usenet: comp.databases.informix

Views are just like tables. The where clause is executed. You should get the same result as if you expanded the SQL syntax by that in the view - use the underlying tables. I dont know of any issues with views in any 7.3 version. MW > -----Original Message----- > From: owner-informix-list@iiug.org > [mailto:owner-informix-list@iiug.org]On Behalf Of Rick Schulte > Sent: Wednesday, 14 May 2003 1:59 a.m. > To: informix-list@iiug.org > Subject: Problem using Views with Multiple tables > > > I have created the following view from 2 tables. When I use the view > the WHERE clause is ignored and all the data is returned i.e.: > > select * from view_cur_acad_stat where cur_acad_stat = 10 ;> > With single table views the where clause is executed. Am I doing > something wrong or is this a limitation of Informix? I'm working with > Informix v 7.3. > > Thanks > > Rick > > CREATE VIEW view_cur_acad_stat> AS > SELECT > acad_status_dim.acad_stat_key AS cur_acad_stat_key, > acad_status_dim.acad_stat AS cur_acad_status, > acad_status_dim.acad_stat_desc AS cur_acad_st_desc, > acad_stat_grp_dim.acad_status_grp AS cur_acad_st_grp, > acad_stat_grp_dim.acad_stat_grp_desc AS cur_acad_grp_desc > FROM > acad_status_dim INNER JOIN acad_stat_grp_dim > ON acad_status_dim.acad_stat = acad_stat_grp_dim.acad_stat; > > grant ALL ON view_cur_acad_stat TO myusername;

↪ replying to Murray Wood

Rick Schulte — 14 May 2003 06:02:21 -0700 — source: Usenet: comp.databases.informix

I have discovered my own solution.... Although I can create a view with the previous code in Informix and will work properly in other databases ( MS SQL Oracle etc). I need to use the following syntax for it to work in Informix with a WHERE clause: use this..... CREATE VIEW cur_acad_stat_dim (cur_acad_stat_key, cur_acad_status, cur_acad_st_desc, cur_acad_st_grp, cur_acad_grp_desc) AS SELECT a.acad_stat_key, a.acad_stat, a.acad_stat_desc, g.acad_status_grp, g.acad_stat_grp_desc FROM acad_status_dim a , acad_stat_grp_dim g WHERE (a.acad_stat = g.acad_stat); Rick "Murray Wood" <murray@quanta.co.nz> wrote in message news:<b9rn4c$96j$1@terabinaries.xmission.com>... > Views are just like tables. The where clause is executed. You should get > the same result as if you expanded the SQL syntax by that in the view - use > the underlying tables. > > I dont know of any issues with views in any 7.3 version. > > MW > > > -----Original Message----- > > From: owner-informix-list@iiug.org > > [mailto:owner-informix-list@iiug.org]On Behalf Of Rick Schulte > > Sent: Wednesday, 14 May 2003 1:59 a.m. > > To: informix-list@iiug.org > > Subject: Problem using Views with Multiple tables > > > > > > I have created the following view from 2 tables. When I use the view > > the WHERE clause is ignored and all the data is returned i.e.: > > > > select * from view_cur_acad_stat where cur_acad_stat = 10 ;> > > > With single table views the where clause is executed. Am I doing > > something wrong or is this a limitation of Informix? I'm working with > > Informix v 7.3. > > > > Thanks > > > > Rick > > > > CREATE VIEW view_cur_acad_stat> > AS > > SELECT > > acad_status_dim.acad_stat_key AS cur_acad_stat_key, > > acad_status_dim.acad_stat AS cur_acad_status, > > acad_status_dim.acad_stat_desc AS cur_acad_st_desc, > > acad_stat_grp_dim.acad_status_grp AS cur_acad_st_grp, > > acad_stat_grp_dim.acad_stat_grp_desc AS cur_acad_grp_desc > > FROM > > acad_status_dim INNER JOIN acad_stat_grp_dim > > ON acad_status_dim.acad_stat = acad_stat_grp_dim.acad_stat; > > > > grant ALL ON view_cur_acad_stat TO myusername;