Re: Outer Join Bug (was It's Incredible)
i forgot to add this with my response. what you might want to try is:
select A.colA, B.colB
from B,
outer A
where A.colA = B.colB
into temp table t1 with no log;
select *
from t1
where colA is null;
again, this is not a bug at all. the 'where' clause is operating on the
values found in the A table, not on the selected set.
> I was recently "bitten" by an Informix outer join problem running under
> V4.0 - I'm not sure if its a bug, or handled differently in newer releases.
> I was trying to use an outer join to find keys in table "B" which did not
> exist in table "A", without using NOT IN or NOT EXISTS. The query looked
> something like:
>
> select A.colA, B.colB from B, outer A
> where A.colA = B.colB and A.colA is null;>
> The problem is in the "A.colA is null" part, and the query returns both the
> normal join items, and the outer items, but colA is null for ALL returned
> records!
>
> I'm pretty sure I've used this technique in Oracle without problems,
> although I didn't go back to verify it.
>
> -Peter Sylvester
>
--
regards,
+----------------------------------------------------------------------------+
| . . | Bob Baskett |
| ... ... | Software Engineer |
| ..... ..... | Business Systems Integration Group |
| .. ... .. | Semiconductor Products Sector |
| . . . | Mesa, AZ |
| | President, Informix Users Group Of Arizona |
| Motorola, Inc. | |
+----------------------------------------------------------------------------+
| Sun 690MP 4.1.3, Online 5.01, 4.10.UD1 Tools, Fourgen v4.10.UC1 |
+----------------------------------------------------------------------------+