Re: NOT EXIST clause

sean@infosol.ie (Sean Birkhead) wrote: :There is probably a null being returned in the subquery which messes :everything up . This is a bug in Informix as far as I'm concerned. I have never found or even heard about any bugs in Informix related to null "values" since they first implemented them (in 1987 was it?). In another post sean@infosol.ie (Sean Birkhead) wrote: :This solution will not work if there is a null value in commonfiled in :any row of table b. : :Try the following : : : select * from test:a where commonfield not in : (select commonfield from live:b : where commonfield is not null) : :or even : : select * from test:a where commonfield not in : (select commonfield from live:b : where length(commonfield) > 0 ) : :Try eliminating any nulls that are returned. This is easier done by :matching specific fields in the query rather than doing a Select *. : :This is certainly the case under SE 5 anyway - maybe they fixed it for :online. They can't possibly "fix" something that works exactly as it should. The behaviour you describe is according to the standard and the way null's should behave. You might want to claim that the spesifications related to null's in the ANSI standard for SQL is a "defect", but that is another issue. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org