I am trying to do a join in an SPL routine and it is not returning what
I am wanting so any help is appreciated:
Informix 9.4FC3 on HPUX

I have a view created that joins 3 tables and that returns a 27k row
view.

Within my SPL, I want to join this view to 3 tables, ensuring I get a
row when any of the 3 tables meets a criteria (but all 3 may not).

I have it currently as "... FROM view, OUTER tab1, OUTER tab2, OUTER
tab3 WHERE v.1 = tab1.1 and v.2 = tab1.2 and v.1 = tab2.1 and v.2 =
tab2.2 etc".

But instead of getting a row from View when any of the 3 tables has a
match, I get EVERY row from view and it dupes tab1,2,3 info to fit.

It is my understanding that if I use an inner join, if any of the 3
tables doesn't have a matching row, the entire row won't be returned and
that is what I am trying to avoid.

Example:

View rows:
1 a some value
1 b some value
2 a some value
....
55 z some value


tab1, tab2, and tab3 have identical columns:
1 a data
1 b data
2 c data
....


I need the return from my SPL to be
view field1, view field2, tab1 field 3, tab2 field 3, tab3 field 3,
calculated field of (tab1-tab2-tab3 field3's), some more columns.

I want to ensure that I will get the row even if tab1 2 OR 3 doesn't
have a matching row (I use NVL(field,0) so arithmetic still works).

What I end up getting is every view rows data and tab 1,2, & 3 rows
repeated even though there are NO match in any of the 3 tables.

What do I need to do to ensure getting every row I need, but not get
erroneous duplicates?

TIA,
Randy K.

Kennedy, Randy wrote:
If I understand this correctly you may want to try this:

select ....
from v, outer tab1, outer tab2, outer tab3
where exists (select 1 from tab1 where v.1 = tab1.1 union all
select 1 from tab2 where v.1 = tab2.1 union all
select 1 from tab3 where v.1 = tab3.1)

This will filter out the rows from the view where there is no match in
either tab1, tab2 or tab3

The other choice is the 'where' clause
"where not (field1 is null and field2 is null and field3 is not null)"
which will drop the records with nulls right across.

HTH

Michael

On Dec 28, 2007 3:39 PM, Kennedy, Randy <RKennedy (AT) scottsdaleaz (DOT) gov> wrote:
The Informix-style OUTER join is a wondrous and weird beastie - and
extremely hard to explain properly.

If you have a two-table OUTER join such as:

SELECT * FROM Tab1, OUTER Tab2
WHERE Tab1.SomeCol = Tab2.SomeCol
AND Tab2.OtherCol = 12

then Tab1 is the dominant table and Tab2 is the subordinate table.
All the rows in the dominant table will be returned. Where there is
one or more rows in the subordinate table matching both the join and
filter conditions, then those rows will be returned in the result set.
Where there are no rows in the subordinate table matching both the
join and filter conditions, then the row from tab1 will be returned
with nulls in place of the values from tab2. Note that if I'd added a
filter such as 'AND Tab1.AnotherCol = 42', then only the rows from
tab1 that match this criterion would be returned - but every row in
tab1 that matches the criterion would be included.

In your example, you have three two-table OUTER joins; these rules
apply to each of the three outer joins.

You said:
Does that mean you want a row from the view to appear when it matches
a row in table 1, or when it matches a row in table 2, or when it
matches a row in table 3, or any combination of these, but not when it
does not match any row in any of the three auxilliary tables.

One way to simplify this is to select with the OUTER joins into an
intermediate temporary table and then apply filters to the temporary
table. In your example, the filter would eliminate those rows from
the temp table for which none of the auxilliary tables matches the row
in the view.

Alternatively, you could use a 7-way UNION:
SELECT * FROM v, tab1, tab2, tab3 WHERE ...
UNION
SELECT * FROM v, tab1, tab2, OUTER tab3 WHERE ...
UNION
....two more single OUTER alternatives...
UNION
SELECT * FROM v, tab1, OUTER tab2, OUTER tab3 WHERE
UNION
...two more double OUTER alternatives...

Using UNION you get duplicate elimination, which would prevent
duplication of the rows where the inner join is satisfied, for
example.

Consider using ISO (ANSI) style OUTER joins - they have different (but
standard) rules for which rows are preserved. Whether it makes it
significantly easier is debatable.

The fact that you're using a view as the dominant table is not an
issue. I've seen worse. In particular, one system I worked on many
years ago specialized in outer self-joins (with views being used
extensively). Those are confusing!

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2007.0914 -- http://dbi.perl.org/
NB: Please do not use this email for correspondence.
I don't necessarily read it every week, even.

On Dec 28, 6:39 pm, "Kennedy, Randy" <RKenn... (AT) scottsdaleaz (DOT) gov>
wrote:
Try this using an inner join to a subselect of a UNION in the FROM
clause top filter and the outer joins to fetch data:

select ...
from view1 v1, (
select t1_s.f1 f1
from tab1 t1_s
where ...
union
select t2.f1
from tab2 t2_s
where ...
union
select t3.f1
from tab3 t3_s
where ... ) sub, outer tab1 t1, outer tab2 t2, outer tab3 t3
where v1.f1. = t1.f1 and v1.f1 = t2.f1 and v1.f1 = t3.f1
and v1.f1 = sub.f1 ...;

if you have 9.xx or later OR with an ANSI join:

SELECT ...
from view1 as v1
left outer join tab1 as t1
on v1.f1 = t1.f1
left outer join tab2 as t2
on v1.f1 = t2.f1
left outer join tab3 as t3
on v1.f1 = t3.f1
where (t1.f1 NOT NULL OR t2.f1 NOT NULL OR t3.f1 NOT NULL);

Art S. Kagel