SEO

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

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, 6:39 pm, "Kennedy, Randy" <RKenn...@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?

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


> TIA,
> Randy K.