SEO

On Dec 28, 2007 3:39 PM, Kennedy, Randy <RKennedy@scottsdaleaz.gov> wrote:
> 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?

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:
> 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).

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@earthlink.net, jleffler@us.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.