Re: Oracle vs Informix...

In article <D6xBAv.B60@zcias2.ziff.com>, barry_roomberg@iacnet.com says... > > >> >>Can you please tell me how the outer join is flawed? I have an >>application that I just ported to Informix, and this would be a big >>problem that I need to be aware of. >> >>Thanks, >> >>Bill MacLean > >It's been about 2 years since I've worked with it, and can't find my notes so >I expect to get flamed for my comments since I can't back them with docs >now. AARRGG! > >Anyway, I expect that the NOT IMPLEMENTED functions are in now, since that >was the easy stuff. The OUTER JOIN was core though, and I expect that will >be there forever. It was (umm, real stretch on the memory here) applying >selection criteria on the outer portion of the join (dummy fields or something >like that), which ended up excluding records that should have been included. >Please don't ask for more since I can't find my docs and no longer have >Informix here to play with. Sorry. > >I got burned really bad, and am still recovering. >MAKE SURE YOU CHECK THE RESULTS OF OUTER JOINS AGAINST WHAT IS >EXPECTED! > We're running Informix 5.x (SunOS) and Oracle 7 (VAX/WMS) at our site. The Informix bug on outer joins is indeed still there. Actually, I don't know if it's the same one you are referring to but there definitely is a JOIN bug. As mentioned the bug causes fewer rows to be retrieved than should be. We are running client server here with PowerBuilder and Crystal Reports report writer. I have tried to step around this bug by "manually coding" the JOIN statement. i.e. I try to code something like "WHERE col 1 = xyzzx or col 1 is NULL...". This type of statement actually causes the Informix process attached to my logon session TO CRASH! I have to logout and get back in again! This is a major problem for a user I was doing some work for. They were trying to join two tables together based on a three field concatenated key. The third field of this key is allowed to hold NULL values. (this key is REALLY stupid but the table design was implemented years ago before I showed up on the scene.) I can't remember if we EVER came up with a query that would return the right result set. I don't know if the above problem is part of the original JOIN problem or a new one. But it sure stinks. -- Mike Dawson Chiron Corp. (IS&T) Emeryville, CA Mike_Dawson@cc.chiron.com Voice: (510) 420-4259 FAX: (510) 420-4820 -------------------------