Arrrrrrrrrrrrr

Posted in 1999

Summary

Steve Peake, used to Oracle, found that a NOT IN subquery on payroll_num between person_load and personnel never returned the row he expected. Reinhard Habichtsberg suggested adding payroll_num IS NOT NULL, noting NULLs sometimes cause problems. Andrew Bland and Richard Auslander asked for a different approach and for details of what failed. Unresolved.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

Steve Peake — Fri, 3 Dec 1999 15:25:16 -0000 — source: Informix-list mailing list archive (1991-1998)

I've been working on this for hours, can anyone explain why this doesn't work: select payroll_num from person_load where payroll_num not in (select payroll_num from personnel) I have a unique payroll_num in the person_load table that doesn't appear in the personnel table, but the query never returns any data. I am used to using oracle, and this would work first time using this. Is there any difference between the statements in oracle and informix that is confusing me here? cheers Steve

↪ replying to Steve Peake

Reinhard Habichtsberg — Fri, 3 Dec 1999 16:49:46 +0100 — source: Informix-list mailing list archive (1991-1998)

Hi Steve, it seems ok. Maybe a little trick helps: select payroll_num from person_load where payroll_num is not null andpayroll_num not in (select payroll_num from personnel) I cannot explain it, but sometimes there are problems with NULL values HTH Reinhard Steve Peake <puppet@dial.pipex.com> schrieb in im Newsbeitrag: 828nd7$2h9pu$1@fu-berlin.de... > I've been working on this for hours, can anyone explain why this doesn't > work: > > select payroll_num from person_load > where payroll_num not in > (select payroll_num from personnel)> > I have a unique payroll_num in the person_load table that doesn't appear in > the personnel table, but the query never returns any data. I am used to > using oracle, and this would work first time using this. Is there any > difference between the statements in oracle and informix that is confusing > me here? > > cheers > > Steve > >

↪ replying to Steve Peake

Andrew Bland — Fri, 03 Dec 1999 15:59:00 +0000 — source: Informix-list mailing list archive (1991-1998)

looks like it should work to me. Why not try a different approach? Steve Peake wrote: > I've been working on this for hours, can anyone explain why this doesn't > work: > > select payroll_num from person_load > where payroll_num not in > (select payroll_num from personnel)> > I have a unique payroll_num in the person_load table that doesn't appear in > the personnel table, but the query never returns any data. I am used to > using oracle, and this would work first time using this. Is there any > difference between the statements in oracle and informix that is confusing > me here? > > cheers > > Steve

↪ replying to Steve Peake

Richard Auslander — Fri, 03 Dec 1999 13:42:50 -0800 — source: Informix-list mailing list archive (1991-1998)

Steve - Would you be willing to let us know what "doesn't work" means? Then we could help you. Rich Steve Peake wrote: > I've been working on this for hours, can anyone explain why this doesn't > work: > > select payroll_num from person_load > where payroll_num not in > (select payroll_num from personnel)> > I have a unique payroll_num in the person_load table that doesn't appear in > the personnel table, but the query never returns any data. I am used to > using oracle, and this would work first time using this. Is there any > difference between the statements in oracle and informix that is confusing > me here? > > cheers > > Steve -- Richard C. Auslander Database Manager AirFlash, Inc. 1733 Woodside Rd., Suite #110 Redwood City, CA 94061 (650) 556-7928 www.airflash.com