View with outer joins
Posted in 1998
Topics: SQL Development & Query Writing, Triggers, Constraints & Referential Integrity, Platform-Specific Issues
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