View with outer joins

Hi, we're using Informix OnLine 5.06.UC1 on Sun Solaris 2.5 and OnLine 7.23.TC9 on Win NT 4. We try to use the following view: create view adr_kpl_v (adr_nr, anrede, titel, vorname, name_1, name_2, name_3, strasse, hsnr, hsnr_zus, plz_plz, plz_ort, plz_ort_zus, plz_land, plz_land_kurz, plz_land_nr, pofa, pf_plz, pf_ort, pf_ort_zus, pf_land, pf_land_kurz, pf_land_nr) as select ad.adr_nr, an.anrede, ti.titel, vo.vorname, ad.name_1, ad.name_2, ad.name_3, ps.bez, ad.hs_nr, ad.hs_nr_z, ps.plz, p1.bez, p1.zusatz, l1.bez, l1.kurz_bez, l1.land_nr, ad.pofa, pf.plz, p2.bez, p2.zusatz, l2.bez, l2.kurz_bez, l2.land_nr from adresse ad, outer (anrede an), outer (titel ti), outer (vorna vo), outer (plz_str ps, land l1, plz p1), outer (plz_pf pf, land l2, plz p2) where ad.anr_nr = an.anr_nr and ad.titel_nr = ti.titel_nr and ad.vorn_nr = vo.vorn_nr and ad.plz_str_nr = ps.plz_str_nr and ps.land_nr = p1.land_nr and ps.ort_nr = p1.ort_nr and ps.plz = p1.plz and ps.erf_kz = p1.erf_kz and p1.land_nr = l1.land_nr and ad.plz_pf_nr = pf.plz_pf_nr and pf.land_nr = p2.land_nr and pf.plz = p2.plz and pf.ort_nr = p2.ort_nr and pf.erf_kz = p2.erf_kz and p2.land_nr = l2.land_nr; The joins via the primary and foreign keys are correctly defined (we checked, re-checked and checked again...) but we get odd results. This is our table of results: (assumed: 'select * from adr_kpl_v where adr_nr = 141') both plz_str_nr and plz_pf_nr in adresse are filled: 1 row (ok) only plz_str_nr in adresse is filled: 0 rows (not ok) only plz_pf_nr in adresse is filled: 1 row (ok) But: if we do a 'select * from adr_kpl_v where adr_nr >= 141 and adr_nr <= 141' (or: '... between 141 and 141') -> 1 row (ok); a 'select ... where adr_nr in (141)' results in 0 rows (not ok). And: if we do run the select (not on the view but in the view) directly (i.e. without the 'create view' stuff), the results are ok! Is this a known problem because of using the same table(s) (land, plz) in different (outer) joins within a view? Any comment is appreciated! I hope the things I wrote here are not too weird... Many regards, Stephan. -- Stephan Stresing Media Systeme Gesellschaft fuer Druck & Verlag mbH Suedwestpark 48 D-90449 Nuernberg Phone +49 911 6809-113 Fax +49 911 6809-110 eMail triple-p.sst@t-online.de