Seek command cannot find the exact string

[abdulmojeb]

hi,

I used the seek command to locate a transaction string on the index file.

SEEK 'MC-15/9022-001E'
IF FOUND()
...
ENDIF

But the program returns a wrong transaction code which is 'MC-15/9012-001E'. Any idea what's wrong?

thanks a lot.

[josipradnik]

Hi,
I would say the index file is outdated or somehow wrong.
Make the new one.

If it continues, try to get the same behaviour with Southwind database, to be sure.

Not finding the solution, let you then make the app package with some simulation table
and your behaviour, so other guys can explore your problem.

Josip

[barrymavin]

I would guess you have trim() in the index,the key you are looking up is not the exact length or you have SOFFSEEK ON. If you ever need help you need to provide details of the index key expression and the key being looked up.

[hmischel@diligentsystems.com]

Hi Abdul,

I created a table with your values and got the same thing you did.

Try this.

select <table>
set exact on
SEEK 'MC-15/9022-001E'

That is working for me.

Herb

[abdulmojeb]

Thanks a lot guys for your help.

Everything is fixed now.

mojeb

[barrymavin]

I'm not seeing any behavior like that at all. It all works as I expect it too.

See screenshot below.

[abdulmojeb]

hi barry,

The problem is fixed by re-indexing the table but later I encounter this error message

to atrno
^
Fatal I/O error reading index file - errno 22 (op=Index Read file=ATRNO at 30303000, size=1396060212)

what does this error mean? But I believe it has something to do with the index file right?

thanks

[barrymavin]

If you let me see the index expression as I asked earlier I can better tell you. You should not be using any trim functions in indexes. The index keys need to all be the same length.

[barrymavin]

The 303030 is hexadecimal for 000 so my suspicions are that you have indexed using a TRIM function.

With SET STRCOMPARE ON (which is the default) all index operations, SQL optimizations and string comparisons are automatically trimmed and compared case insensitive.

For example:

m_searchvalue = "ALFKI"
select * from customers where trim(upper(customerid))=trim(upper("&m_searchvalue "))

with STRCOMPARE ON is just:

m_searchvalue = "ALFKI"
select * from customers where customerid="&m_searchvalue"

This saves a lot of messy coding.

If if you don't want this behavior turn it off.

[abdulmojeb]

I tried that before but it cannot find some records because of extra space on the expression. Please look at this

create index plate on artrans (atplate,atcd)
explain select * from artrans where atplate='RNW340' and atcd='S'

the field ATPLATE has maximum of 7 characters length. The output is

Optimized WHERE condition for table 'artrans' using index tag 'PLATE'
Table 'artrans' has 146127 records
SEEK 'RNW340 S'
SCAN WHILE ATPLATE+ATCD = 'RNW340 S'
Total I/O read operations was 4
3 records selected in 2ms

3 records are selected using the sql select but using the scan command no record is selected

SEEK 'RNW340S'
SCAN REST WHILE atplate+atcd = 'RNW340S'

ENDSCAN

thanks