RE: Subquery not returning correct results

One word: nulls Without knowing anything about you version, schema, etc. I'd venture to say that some of col1-col5 in myTable or myOtherTable contain nulls that are throwing things off. -----Original Message----- From: joel_anderson@prosolution.com [SMTP:joel_anderson@prosolution.com] Posted At: Thursday, July 08, 1999 10:04 AM Posted To: Informix Conversation: Subquery not returning correct results Subject: Subquery not returning correct results I have an SQL statement that does a subquery that returns a count of 0 when it should return a count of 200000+. The statement looks like this: select count(*) from myTable where col1 || col2 || col3 || col4 || col5 not in (select col1 || col2 || col3 || col4 || col5 frommyOtherTable); The above statement returns a count of 0 when it should return a count of 200000+. If I change the "not in" to "in" in the statement, I get a count of 33000 returned. myTable has over 200000 rows. myOtherTable has 5500 rows. If I remove a few hundred rows from myOtherTable, I then get the expected count of 200000+. This looks like a bug. Is there a work around that anyone knows of? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

Generally when I have a problem like this I solve it by not using IN I say : SELECT COUNT(*) FROMtabA WHERE ( SELECT COUNT(*) FROM tabB WHERE tabA.Key = tabB.key ) = 0 Whilst probably not as efficent it can do the job if you know you have a mixture of data and null values. Scott Black <sblack@elsouth.com> wrote in message news:7m2h4i$5s8$1@news.xmission.com... > > One word: > nulls > > Without knowing anything about you version, schema, etc. I'd venture to > say that some of col1-col5 in myTable or myOtherTable contain nulls that > are throwing things off. > > -----Original Message----- > From: joel_anderson@prosolution.com > [SMTP:joel_anderson@prosolution.com] > Posted At: Thursday, July 08, 1999 10:04 AM > Posted To: Informix > Conversation: Subquery not returning correct results > Subject: Subquery not returning correct results > > I have an SQL statement that does a subquery that returns a > count of 0 > when it should return a count of 200000+. The statement looks > like > this: > > select count(*) from myTable > where col1 || col2 || col3 || col4 || col5 not in > (select col1 || col2 || col3 || col4 || col5 from> myOtherTable); > > The above statement returns a count of 0 when it should return a > count > of 200000+. If I change the "not in" to "in" in the statement, > I get a > count of 33000 returned. myTable has over 200000 rows. > myOtherTable > has 5500 rows. If I remove a few hundred rows from > myOtherTable, I > then get the expected count of 200000+. > > This looks like a bug. Is there a work around that anyone knows > of? > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.