VIEW - Problems
Well, my last INFORMIX-problem was solved by this group, but
I have a new problem with Informix-VIEWs:
There is a View defined over ( for simplicity ) 2 tables (Regard the OUTER! ):
CREATE TABLE A (
A_ID SERIAL (1) ,
A_NAME CHARACTER(10),
B_ID INTEGER );
CREATE TABLE B (
B_ID SERIAL (1) ,
B_NAME CHARACTER(10) ,
B_NR INTEGER );
CREATE VIEW VAS SELECT
A.A_ID,
A.A_NAME,
B.B_ID,
B.B_NAME,
B.B_NR
FROM
A,
OUTER B
WHERE
A.B_ID = B.B_ID;
Mostly selects against the View V work as expected. The only case when they do
not is when I specify a WHERE-clause to a column of B.
SELECT * FROM V WHERE B_NR = 6
This SELECT returns data that has B_NR entries which are not equal to 6 ! In
these result entries simply all the columns of B are omitted ( NULL, I think).
If - as mentioned in the tutorial - a view should behave like a table, this
here is the wrong behaviour!!
I know that the query would return what I expect if I would not use the
OUTER-clause in the View-definition. But of course the View is also used for
accesses using WHERE clauses against columns of A where I need the OUTER-clause
to get the correct result!
In practice these kind of Views reach over some 6 or 8 tables and I can not write
extra Views (with and without OUTER for certain tables) for each combination of
WHERE-clauses!
Anybody got an idea to solve the problem?
Is it an Informix-bug or am I just demanding too much from Views?
Thanks in advance for any good ideas !
--
==========================================================================
/ Achim Reiners Software Engineering \\
| /M/A/I Deutschland GmbH |
| Softwarezentrum Koeln Phone: +49 221 956400-40 |
| Mathias-Brueggen-Str. 85 Fax: +49 221 956400-69 |
\\ 50829 Koeln E-Mail: ar@mai.de /
\\_________________________________________________________________________/