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)

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

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)
>
> 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.

Which oncheck options?

> Does anyone have idea other than dropping and recreating indexes?

What size is the table? What is the query plan (SET EXPLAIN ON)?
Which version (complete version) are you using? Which platform? What
is the table schema? How many rows of data in the table?

**Don't hurry to run UPDATE STATISTICS**

When did you last run UPDATE STATISTICS on the table? What options
did you use? What are the distributions? Please capture this
information *before* runnig UPDATE STATISTICS! Then consider whether
to run UPDATE STATISTICS.

Wrong results are always alarming. Report the issue to IBM Informix
Tech Support.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

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

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@bloomber g.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

Set the explain plan on. Rerun the query. Note which index the query
is using. Force it to do a scan using optimizer hinting.

select {+ full(ledgdet) } ...

I think that is the correct syntax. If you get all 40 rows then drop
and rebuild the index found earlier and then try the query again. If
this fixes it then it was the index. If not then if might actually be
an informix bug.
Or a problem with the sql that is typed in. Try retyping the query or
using the "od" command to find funny characters that may be affecting
the execution of the query.

bardavidh@yahoo.com (Hakan Bardavid) wrote in message news:<7db4101a.0408180557.669c493d@posting.google. com>...
> 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)
>
> 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@bloomber g.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