Erratic behavior of "Not In" in a Select

Posted in 2010

GUSTAVO ECHENIQUE — Fri, 03 Sep 2010 23:04:59 — source: IIUG Forums & Mailing Lists

Hello Forum! I have the following query: "select pc.codcuenta, pc.descripcion, sum( tc.importe ) importe from temp_conceptos tc, outer( cptos_contab_ctas ccc, outer plan_cuentas pc ) where tc.idconcepto NOT IN ( 5, 9, 10, 30, 31, 32, 40, 41, 62, 306, 15, 20, 21, 22, 25, 26, 33, 35, 38, 65, 66, 303, 308, 310, 432, 433, 434 ) ----, 23, 24, 27, 29, 100, 101, 291, 292, 293, 294, 295, 299 ) and tc.idconcepto = ccc.idconcepto and tc.idsubconcepto = ccc.idsubconcepto and ccc.idcta_ingpasivfl = pc.idcuenta group by 1, 2 order by 1" It returns me a certain amount of records. Note that is commented out a part of the code for the "NOT IN". If I uncomment that part, the query returns fewer records than me on the first run, but I know conclusively that these codes do not exist in table temp_conceptos. I'm confused with this behavior of "NOT IN" can anyone help me?

↪ replying to GUSTAVO ECHENIQUE

Fernando Nunes — Sat, 04 Sep 2010 22:07:42 — source: IIUG Forums & Mailing Lists

On Sat, Sep 4, 2010 at 12:05 AM, GUSTAVO ECHENIQUE < gustavo.echenique@cemdo.com.ar> wrote: > Hello Forum! > > I have the following query: > "select pc.codcuenta, pc.descripcion, sum( tc.importe ) importe > > from temp_conceptos tc, outer( cptos_contab_ctas ccc, outer plan_cuentas pc > ) > > where tc.idconcepto NOT IN ( 5, 9, 10, 30, 31, 32, 40, 41, 62, 306, 15, 20, > 21, 22, 25, 26, 33, 35, 38, 65, 66, 303, 308, 310, 432, 433, 434 ) ----, > 23, > 24, 27, 29, 100, 101, 291, 292, 293, 294, 295, 299 ) > > and tc.idconcepto = ccc.idconcepto > > and tc.idsubconcepto = ccc.idsubconcepto > > and ccc.idcta_ingpasivfl = pc.idcuenta > > group by 1, 2 > > order by 1" > > It returns me a certain amount of records. Note that is commented out a > part > of the code for the "NOT IN". > If I uncomment that part, the query returns fewer records than me on the > first > run, but I know conclusively that these codes do not exist in table > temp_conceptos. > I'm confused with this behavior of "NOT IN" can anyone help me? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Can you provide an example... Something we can use to reproduce the problem? Regards -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --00163630f8dd8a33f0048f7646f2