dnb@fltk.UUCP (David Buonomo) (08/22/89)
I have a .DBF file indexed on 2 numeric fields of 4 and 6 digits respectively. More specifically, the index is built by: index on str(invnum,4)+str(seqnum,6) to idx_file I would like to do a seek specifying only the first index. I am currently unable to accomplish this (result of seek is EOF). Is this possible to do and if so, how?
blank@silver.bacs.indiana.edu (08/22/89)
>I have a .DBF file indexed on 2 numeric fields of 4 and 6 digits respectively. >More specifically, the index is built by: > > index on str(invnum,4)+str(seqnum,6) to idx_file > >I would like to do a seek specifying only the first index. I am currently >unable to accomplish this (result of seek is EOF). Is this possible to do >and if so, how? I have had to similar searches, and I have tried this technique. When I create the index, I put a delimiter in the string. So, for your example, I would index on: index on str(invnum,4)+"*"+str(seqnum,6) to idx_file Then to do a search on invnum only, i would set exact off and seek str(invnum)+"*" If you do this, you will have to add the delimiter to your seeks that currently work for finding both numbers, like: seek str(invnum)+"*"+str(seqnum,6) Also, make sure that the number of spaces in front of the number are the same. In fact, all of these types of searches, I add a ltrim to avoid the problem: index on ltrim(str(invnum))+"*"+ltrim(str(seqnum)) seek ltrim(str(invnum))+"*" -doug
timk@xenitec.uucp (Tim Kuehn) (08/22/89)
In article <238@fltk.UUCP> dnb@fltk.UUCP (David Buonomo) writes: >I have a .DBF file indexed on 2 numeric fields of 4 and 6 digits respectively. >More specifically, the index is built by: > > index on str(invnum,4)+str(seqnum,6) to idx_file > >I would like to do a seek specifying only the first index. I am currently >unable to accomplish this (result of seek is EOF). Is this possible to do >and if so, how? IF you set SOFTSEEK ON, and then do your seek, the record pointer will be left at either the desired entry, or at the first entry *greater* than the specified seek string. You would need to setup your seek condition so: set softseek on seek str(invnum,4) + space(6) +-----------------------------------------------------------------------------+ |Timothy D. Kuehn timk@xenitec | |TDK Consulting Services !watmath!xenitec!timk | |871 Victoria St. North, Suite 217A | |Kitchener, Ontario, Canada N2B 3S4 (519)-741-3623 | |DOS/Xenix - SW/HW. uC, uP, DBMS. Satisfaction Gauranteed| +-----------------------------------------------------------------------------+
AR.HFN@forsythe.stanford.edu (Hooshyar Naraghi) (08/23/89)
In article <238@fltk.UUCP>, dnb@fltk.UUCP (David Buonomo) writes: >I have a .DBF file indexed on 2 numeric fields of 4 and 6 digits respectively. >More specifically, the index is built by: > > index on str(invnum,4)+str(seqnum,6) to idx_file > >I would like to do a seek specifying only the first index. I am currently >unable to accomplish this (result of seek is EOF). Is this possible to do >and if so, how? Unfortunately you did not specify what your SEEK command was, however, I am going to guess what it is by looking at your INDEX command. The STR function takes the numeric field and if the value of this field is LESS THAN 4 DIGITS, say 123, 10, or 5, then it will add leading blanks to make it 4 characters long when returning a character-type data. For example, suppose you have a record whose INVNUM field has value of numeric 123. If you SEEK "123" you will not find this record. However, if you SEEK " 123", the pointer will point to the record. Please note there was a blank space before 123 (in character type). The best thing to do is to index the file by removing the leading blanks. LTRIM function will accomplish that. I know dBXL and FOX and of course dBASEIII+ support this function, but I am not sure about clipper since I do not use it. The command looks like: INDEX on LTRIM(STR(invnum,4)) + LTRIM(STR(seqnum,6)) to idx_file Now, if you seek "123", the pointer will point to the record whose INVNUM value is 123. I hope this can be useful to you. Thanks for your patience. Hooshyar Naraghi Stanford University AR.HFN@forsythe.stanford.edu (415) 723-3790
awd@dbase.UUCP (Alastair Dallas) (08/23/89)
In article <238@fltk.UUCP>, dnb@fltk.UUCP (David Buonomo) writes: > I have a .DBF file indexed on 2 numeric fields of 4 and 6 digits respectively. > More specifically, the index is built by: > > index on str(invnum,4)+str(seqnum,6) to idx_file > > I would like to do a seek specifying only the first index. I am currently > unable to accomplish this (result of seek is EOF). Is this possible to do > and if so, how? Is this a Clipper problem? In dBASE, as long as SET EXACT is OFF, you can SEEK "A" and find "Alastair". In this case you can SEEK STR(4892,4) and find "4892100321". In other words, your approach seems valid, from the information you've given. Perhaps with more information, the problem will be clearer... /alastair/
awd@dbase.UUCP (Alastair Dallas) (08/24/89)
In article <20900014@silver>, blank@silver.bacs.indiana.edu writes: > > Also, make sure that the number of spaces in front of the number are > the same. In fact, all of these types of searches, I add a ltrim to > avoid the problem: > > index on ltrim(str(invnum))+"*"+ltrim(str(seqnum)) > seek ltrim(str(invnum))+"*" > > -doug INDEX ON LTRIM(..) is a bad idea. It is very important that index keys be fixed length, and TRIM() or LTRIM() do not support this: .INDEX ON LTRIM(STR(N)) TO Foo .LIST STR(N), LTRIM(STR(N)), LEN(LTRIM(STR(N))) 1 13 13 2 2 12345 12345 5 3 233 233 3 4 166 166 3 5 32 32 2 6 0 0 1 Indexes must have fixed length keys. Quoting the dBASE IV manual (p2-150): "dBASE IV does not prohibit you from creating an index with a variable-length key, but the index may not be reliable." /alastair/ N STR(N) Key Length 13 "13" 2 123456 "123456" 6 0
jbrown@herron.uucp (Jordan Brown) (08/26/89)
In article <202@dbase.UUCP>, awd@dbase.UUCP (Alastair Dallas) writes: > INDEX ON LTRIM(..) is a bad idea. It is very important that index keys be > fixed length, and TRIM() or LTRIM() do not support this: > ... > Indexes must have fixed length keys. Quoting the dBASE IV manual (p2-150): > "dBASE IV does not prohibit you from creating an index with a variable-length > key, but the index may not be reliable." Gee, we thought that one of the improvements of dBASE III over dBASE II was that you could have TRIMs in the index expression. (That's half of what all that horrible code in the parser is there for - to calculate the maximum possible length of the expression for use in sizing index keys.) There certainly were cases where this didn't work right - where it couldn't figure out the right maximum - but TRIM wasn't one of them. Clipper has a no-TRIM restriction on indexes, so avoiding TRIM is a good idea. Theirs reason is that to calculate the length of an index key they evaluate it on a blank record; the resultant length is used. Obviously TRIM will return a somewhat smaller value for blank fields than for filled fields.