Re: VIEW - Problems

from Achim Reiners: > I have a new problem with Informix-VIEWs: > There is a View defined over ( for simplicity ) 2 tables (Regard the OUTER! ): create table stuff deleted.. > CREATE VIEW V> AS 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! > The problem is not the view but the way outer joins behave. In my opinion, and based on my experience with Informix and a few other databases, you are getting the correct result. I know there is disagreement on how outer joins should work but this seems to be the way they work. The following select statement will return the same results as your view: 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 AND B_NR = 6; The select statement is asking for: 1) ALL the rows from A, ( there is no condition on A ) 2) The rows from B that meet the condition B_NR = 6 3) Join the results of 1 and 2. Since it is an outer join, NULLS will be returned from B where there is no match. > Anybody got an idea to solve the problem? > Is it an Informix-bug or am I just demanding too much from Views? I am sorry that this does not solve your problem. I just wanted to point out that this is a feature of outer joins ( and a feature I use ). The following is an example of why outer joins should work this way. Suppose you had a customer and credit table. The credit table contains a status field with G-good, B-bad, P-pendng etc ... To get a report of all customers, but only show a status if it was bad, this feature of outer joins is needed. (It could also be done with unions). The select would be : select cust.name, credit.status from cust, outer credit where cust.id = credit.id and credit.status = "B" This prints all customers names, and the status is NULL or a B. Regards - Lester ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Providing Informix Database Tools and Consulting # #############################################################################