Xref: utzoo comp.sys.ibm.pc:15885 comp.databases:1028
Path: utzoo!utgpu!water!watmath!clyde!att!osu-cis!tut.cis.ohio-state.edu!husc6!uwvax!oddjob!gargoyle!ddsw1!karl
From: karl@ddsw1.UUCP (Karl Denninger)
Newsgroups: comp.sys.ibm.pc,comp.databases
Subject: Re: dBase tricky INDEXing question...
Summary: Length of the first substring is not a constant
Message-ID: <1106@ddsw1.UUCP>
Date: 25 May 88 01:45:31 GMT
References: <529@etn-rad.UUCP>
Reply-To: karl@ddsw1.UUCP (Karl Denninger)
Organization: Macro Computer Solutions, Inc., Mundelein, IL
Lines: 56

In article <529@etn-rad.UUCP> markus@etn-rad.UUCP (Markus Richardson) writes:
..It seems like I am using a database in a very common way, but I cannot for
..the life of me figure out how to get dBase to index it correctly.  
..
..My trans[action].dbf file has five fields.  The first two fields I wish to 
..be combined together (for the index) in order to have the records in an order 
..based upon the ordering of the first two fields.  Simple enough, right?  I 
..guess if both fields were of the same data type it would be no problem,
..but as it is the first field (account number) is a numeric type and the 
..second field (date of transaction) is a date type.
..
..acct_no = 1000
..SEEK acct_no
..IF .NOT. FOUND() THEN
..	RETURN
..ENDIF
..DO WHILE (acct_no = trans->account_no)
..	? "account_no="+trans->account_no+"  date="+DTOC(trans->date)
..	SKIP
..ENDDO
..RETURN
..
..Attempt 1:
..SET EXACT OFF
..USE Trans ALIAS Trans
..INDEX ON LTRIM(STR(trans->account_no))+DTOC(trans->date) TO Trans.ndx
..SEEK LTRIM(STR(account_no))          && Finds the correct matching records but
..                                      && they are NOT in order of trans->date!

Yep.

..What am I doing incorrectly?  This seems like such a common scenario for
..data retrieval: ordering on a combination numeric+date index.
..
..FYI, I am using dBase ]I[ + version 1.1 .

The problem is that your first component of the index expression does not
have a constant length.  That is, "LTRIM(STR(xxxx))" is not a constant
length string.

The result of this is:

12388.05.23		- Record number 123, 05/23/88
188.05.23		- Record number 1, 05/23/88

See where this might be a problem?

This "feature" can be avoided by not using LTRIM -- then the string,
is always 10 characters in length and the index will be correctly built.

Enjoy!

----
Karl Denninger                 |  Data: +1 312 566-8912
Macro Computer Solutions, Inc. | Voice: +1 312 566-8910
...ihnp4!ddsw1!karl            | "Quality solutions for work or play"