Re: Bug in ADO-Driver when querying with outer against NOT NULL-Fields

Posted in 2004

scottishpoet — 15 Mar 2004 17:08:33 -0800 — source: Usenet: comp.databases.informix

Looks like you are using a client tool that is trying to be too clever!! you could try and use the null value replacement function to pass an empty string instead of NULL. Don't have the manual to handbut I think it wouldbe something like : SELECT company.untname, company.untid, nvl(comp_loc.locid,"") FROM company, outer comp_loc WHERE company.untid = comp_loc.untid Daniel Voelkel <daniel.voelkel.NG@gmx.net> wrote in message news:<c34fcr$53v$02$1@news.t-online.com>... > Hi there, > > I have testet this bug with several OLEDB-Drivers (2.70 TC5 ? 2.81 TC3). > I have the following tables: > > company ( > compid SERIAL(1) NOT NULL, > compname CHAR(32) NOT NULL, > lastchange CHAR(20)) > > AND > > dvoelkel.comp_loc ( > compid INTEGER NOT NULL, > locid INTEGER NOT NULL, > lastchange CHAR(20)) > > When joining with: > > SELECT company.untname, company.untid, comp_loc.locid > FROM company, outer comp_loc > WHERE company.untid = comp_loc.untid> > I receive no rows or an error like ?a column which doesn?t allow NULL?s > can?t be NULL?. > > > The following query works fine: > > SELECT company.untname, company.untid, comp_loc.lastchange > FROM company, outer comp_loc > WHERE company.untid = comp_loc.untid> > It seems, that the resultfields of the query are checked against the > field-properties the fields are selected from. So the first query > doesn?t work, because ?locid? is set to NOT NULL. But it can be null in > the query. This is what I wan?t the query for. > The second query retrieves the rows as needed, because the field > ?lastchange? isn?t set to NOT NULL. I have the same results when using > LEFT OUT JOIN instead of OUTER. > Can anyone confirm this? Does anyone has a solution or workaround for this? > > Thanks in advance, > Daniel

↪ replying to scottishpoet

Daniel Voelkel — Tue, 16 Mar 2004 08:13:50 +0100 — source: Usenet: comp.databases.informix

scottishpoet wrote: > Looks like you are using a client tool that is trying to be too > clever!! > SELECT company.untname, company.untid, nvl(comp_loc.locid,"") > FROM company, outer comp_loc > WHERE company.untid = comp_loc.untid Hi, Yes! This works fine for me. And I have to correct my posting. There seems to be no bug in the driver. The query works fine when using Visual Studio. The error occured in Delphi6. I'll check this with Delphi7 too. Thanks, for the tip, Daniel

↪ replying to Daniel Voelkel

scottishpoet — 16 Mar 2004 13:28:37 -0800 — source: Usenet: comp.databases.informix

Thats Great news Daniel. I suspect delphi is trying to be clever and reading the system tables to see if the values Informix passes back to it can be NULL or not. No doubt they have solutions for the OUTER join situation, I suspect they might say you need to remove the NOT NULL constraint. Daniel Voelkel <daniel.voelkel.NG@gmx.net> wrote in message news:<c3699e$5vs$07$1@news.t-online.com>... > scottishpoet wrote: > > Looks like you are using a client tool that is trying to be too > > clever!! > > SELECT company.untname, company.untid, nvl(comp_loc.locid,"") > > FROM company, outer comp_loc > > WHERE company.untid = comp_loc.untid> > Hi, > Yes! This works fine for me. And I have to correct my posting. There > seems to be no bug in the driver. The query works fine when using Visual > Studio. The error occured in Delphi6. I'll check this with Delphi7 too. > > Thanks, for the tip, > Daniel