Re: Outer Join Bug (was It's Incredible); also, Informix vs. Oracle

I have occasionally used outer joins in both Informix and Oracle, and after initially debugging my own code, I have never had either product give me "wrong" answers. The original poster claimed that Informix goofed up on him two years ago, but would give no other details, not even an example of a query that produced erroneous results. This sounds like FUD (spreading Fear, Uncertainty, and Doubt) to me, rather than any sort of useful communication. I don't know what the SQL standard (if any) for outer join syntax is. I DO know that since these two products use quite different syntax to specify outer joins, such queries are decidedly non-portable without manual conversion. I also know that Oracle will not do two-stage outer joins in one query. By this I mean parent-child-grandchild reports, where either the child or grandchild (and child) may be missing. To accomplish this, I needed to create two views: one for the parent-child join with outer join on the child, one with the view-grandchild join with outer join on the grandchild. (I know, I could have just done the second query, rather than creating a view for it; but I was building a script for use by others and was able to have the script contain a simple, unjoined query on the view.) Once I used this two step approach, Oracle did just fine. I don't recall needing to do something quite that complex in Informix. -- R. Alan Popiel Internet: alan@den.mmc.com Lockheed Martin, SLS Voice: 303-977-9998