Re: Informix returning inconsistent resultset

It is kind of my mistake that I didn't look at query carefully before posting my question. I specify those two indexes(2279563,2279564) twice. For some reason , informix is returning me unique rows out of my query. Thank you all for your answers. "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:<pan.2004.08.18.11.13.04.549849.1355@bloomberg.net>... > On Wed, 18 Aug 2004 09:57:03 -0400, Hakan Bardavid wrote: > > > The following simple query I have , is returning 38 rows , although I know > > that it should return me 40 rows. > > > > select * from ledgdet where lindex in > > (2279561,2245322,2279562,2245323, > > 2242315, > > 2242319,2244911,2244915, > > 2256408 ,2256412 ,2279565, > > 2242316,2242320,2244912, > > 2244916, 2256409 ,2256413 ,2279566, > > 2279563,2279564,2245324,2245325, > > 2242317,2242318,2242321,2242322,2244913,2244914, > > 2244917,2244918,2256410,2256411,2256414,2256415, > > 2256417,2256418,2279563,2279564,2279567,2279568)> > Is it possible that you built the query above outside dbaccess in a script > and that the query is one or two VERY long lines? Perhaps you just blew > dbaccess's line buffer length? If so try breaking it up as it appears in the > email into one to four lindex values per line and see what happens. > > Art S. Kagel > > > It is not returning me the rows associated with lindexes 2279563,2279564 > > > > if I execute the following query > > select * from ledgdet where lindex in (2279563,2279564) I am getting those> > two rows. > > > > > > I am executing this query using dbaccess utility. In addition to that, I > > already run oncheck on this table. It didn't report any errors. > > > > Does anyone have idea other than dropping and recreating indexes? > > > > Any help will be greatly appreciated. > > Thanks in advance

On Thu, 19 Aug 2004 09:20:20 -0400, Hakan Bardavid wrote: Ahh, missed that. Not "for some reason", that's the way SQL works everywhere if it's working correctly. Specifying a filter criteria multiple times does not get you multiple copies of the record. Art S. Kagel > It is kind of my mistake that I didn't look at query carefully before > posting my question. > I specify those two indexes(2279563,2279564) twice. For some reason , > informix is returning me unique rows out of my query. Thank you all for your > answers. > > "Art S. Kagel" <kagel@bloomberg.net> wrote in message > news:<pan.2004.08.18.11.13.04.549849.1355@bloomberg.net>... >> On Wed, 18 Aug 2004 09:57:03 -0400, Hakan Bardavid wrote: >> >> > The following simple query I have , is returning 38 rows , although I >> > know that it should return me 40 rows. >> > >> > select * from ledgdet where lindex in >> > (2279561,2245322,2279562,2245323, >> > 2242315, >> > 2242319,2244911,2244915, >> > 2256408 ,2256412 ,2279565, >> > 2242316,2242320,2244912, >> > 2244916, 2256409 ,2256413 ,2279566, >> > 2279563,2279564,2245324,2245325, >> > 2242317,2242318,2242321,2242322,2244913,2244914, >> > 2244917,2244918,2256410,2256411,2256414,2256415, >> > 2256417,2256418,2279563,2279564,2279567,2279568)>> >> Is it possible that you built the query above outside dbaccess in a script >> and that the query is one or two VERY long lines? Perhaps you just blew >> dbaccess's line buffer length? If so try breaking it up as it appears in >> the email into one to four lindex values per line and see what happens. >> >> Art S. Kagel >> >> > It is not returning me the rows associated with lindexes 2279563,2279564 >> > >> > if I execute the following query >> > select * from ledgdet where lindex in (2279563,2279564) I am getting>> > those two rows. >> > >> > >> > I am executing this query using dbaccess utility. In addition to that, I >> > already run oncheck on this table. It didn't report any errors. >> > >> > Does anyone have idea other than dropping and recreating indexes? >> > >> > Any help will be greatly appreciated. >> > Thanks in advance