Where clause of select on view is ignored

Hi, We work with an informix database on Solaris. We created this view: create view v_kantoren as select k.agentgencode, k.agentkantoornr, s.superagentgencode,s.superagentkantoornr, c.agenttocode, c.tocode from t_rakantoorcode c inner join t_rakantoor k on c.agentgencode = k.agentgencode and c.agentkantoornr = k.agentkantoornr left join t_rakantoorsuper s on s.agentgencode = k.agentgencode and s.agentkantoornr = k.agentkantoornr; agentgencode and agentkantoornr together are the primary key of the table t_rakantoor If we do: select * from v_kantoren, then we get the correct number of rows. If we do: select * from v_kantoren where ..., with every where clause we still get alle the rows of the view as a result. So the where clause is simple ignored... Found someone in Google Groups with the same problem: http://groups.google.be/group/comp.databases.informix/browse_thread/thread/425bd5f9212f9de7/bcca0c25b2be12f8?lnk=st&q=informix+solaris+view+%2Bwhere&rnum=4&hl=nl#bcca0c25b2be12f8 He suggests as a solution to replace the inner joins with outer joins. But in our case, we have a left join as well and that can't easily be replaced with an outer join? Anyone knows a possible solution? Veerle

veerleverbr@hotmail.com wrote: > Hi, > > We work with an informix database on Solaris. We created this view: > create view v_kantoren as > select k.agentgencode, k.agentkantoornr, s.superagentgencode,> s.superagentkantoornr, c.agenttocode, c.tocode > from t_rakantoorcode c > inner join t_rakantoor k on c.agentgencode = k.agentgencode and > c.agentkantoornr = k.agentkantoornr > left join t_rakantoorsuper s on s.agentgencode = k.agentgencode and > s.agentkantoornr = k.agentkantoornr; > > agentgencode and agentkantoornr together are the primary key of the > table t_rakantoor > > If we do: select * from v_kantoren, then we get the correct number of > rows. > If we do: select * from v_kantoren where ..., with every where clause > we still get alle the rows of the view as a result. So the where clause > is simple ignored... > > Found someone in Google Groups with the same problem: > http://groups.google.be/group/comp.databases.informix/browse_thread/thread/425bd5f9212f9de7/bcca0c25b2be12f8?lnk=st&q=informix+solaris+view+%2Bwhere&rnum=4&hl=nl#bcca0c25b2be12f8 > He suggests as a solution to replace the inner joins with outer joins. > But in our case, we have a left join as well and that can't easily be > replaced with an outer join? VERSION and platform information please! Someone may know of a version or platform specific bug/fix but without the info on what you are running... A LEFT JOIN is an outer join. It is a short hand syntax for LEFT OUTER JOIN. If you were to issue the SELECT underlying the join with the WHERE clause directly (ie without using the VIEW) what happens? Art S. Kagel > Anyone knows a possible solution? > > Veerle >