Re: Problem using Views with Multiple tables
Hi,
I did some testing on Solaris 7 with IDS 7.31.UD5, and cannot reproduce the
problem. You're going to need to work out whether this is something that
can be fixed by an upgrade from your current version - you didn't give
details of what the version really is (7.3 covers about 20 releases over a
period of perhaps five years; the extra digits are *all* relevant, as is
the platform). Here's my test case, reverse engineered from your example:
CREATE TABLE acad_status_dim
(
acad_stat_key INTEGER NOT NULL PRIMARY KEY,
acad_stat INTEGER NOT NULL,acad_stat_desc VARCHAR(60) NOT NULL
);
CREATE TABLE acad_stat_grp_dim
(
acad_stat INTEGER NOT NULL PRIMARY KEY,
acad_status_grp CHAR(5) NOT NULL,acad_stat_grp_desc VARCHAR(60) NOT NULL
);
CREATE VIEW view_cur_acad_stat ASSELECT
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;
INSERT INTO acad_status_dim VALUES(1, 10, 'Key 1, Status 10');
INSERT INTO acad_status_dim VALUES(2, 9, 'Key 2, Status 9');
INSERT INTO acad_stat_grp_dim VALUES(10, 'A0000', 'Stat 10, Grp A0000');
INSERT INTO acad_stat_grp_dim VALUES(9, 'B99999', 'Stat 9, Grp B9999');
SELECT * FROM view_cur_acad_stat WHERE cur_acad_status = 10;
SELECT * FROM view_cur_acad_stat;
The first SELECT (on the view with a WHERE clause) returns one row of data
(1,10, "Key 1, Status 10", "A0000", "Stat 10, Grp A0000").
The second SELECT (on the view with no WHERE clause) returns two rows of
data, the one above plus (2, 9, "Key 2, Status 9", "B9999", "Stat 9, Grp
B9999").
I believe both of those are the correct expected results and do not
reproduce your problem.
If you have a more recent version of IDS than I'm using, then you have a
regression. If you have an older version, you most probably have a genuine
bug, but one that has already been fixed in later versions, and therefore
an upgrade is in order. If you have the same version on a different
platform, you might have a platform-specific bug; if you have the same
version on Solaris 7, then my test case is not sufficiently accurate or
complex. However, as things stand, I think that you do not have a current
bug - but without your detailed version information, no-one can be sure.
PS: To all who are reading this - please note that when you run into a
problem with Informix, a nice simple complete self-contained test case like
the one I just created greatly aids those who might help you in analyzing
the problem.
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
|---------+---------------------------->
| | Jonathan Leffler |
| | <jleffler@earthli|
| | nk.net> |
| | |
| | 05/14/2003 07:44 |
| | AM |
| | |
|---------+---------------------------->
>---------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: Rick Schulte <schulte_rick@hotmail.com> |
| cc: |
| Subject: Re: Problem using Views with Multiple tables |
| |
>---------------------------------------------------------------------------------------------------------------------------------------------|
Rick Schulte wrote:
> 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);
Then that's a bug. If you have Tech Support, please report it through
those channels. It carries more weight if there's a customer case
with the issue.
> 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;
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Inf