Xref: utzoo comp.sys.ibm.pc:15947 comp.databases:1038 Path: utzoo!attcan!uunet!lll-winken!lll-tis!ames!mailrus!tut.cis.ohio-state.edu!bloom-beacon!gatech!hubcap!ncrcae!ncr-sd!crash!pnet01!benh From: benh@pnet01.cts.com (B. Humphreys) Newsgroups: comp.sys.ibm.pc,comp.databases Subject: Re: dBase tricky INDEXing question... Message-ID: <3026@crash.cts.com> Date: 26 May 88 15:37:22 GMT Sender: news@crash.cts.com Organization: People-Net [pnet01], El Cajon CA Lines: 100 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! >>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 . Karl Denninger writes: >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 problem is not only your first commponent of your index expression but also the second component: DTOC(trans->date) > >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 example is really inaccurate. First of all, DTOC() does not convert it's arguement to a different format than the operand. For example, 05/23/88 does not become 88/05/23, let alone 88.05.23. Secondly, it's not the record number that you're indexing on, it's the account number, correct? >>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. This observation, however, is correct. You must make sure that all index keys generated by your index expression are of the same length. For example: Index expression: RTRIM(last_name) + ", " + RTRIM(last_name) Last Name First Name Index expression Len -------------------- -------------------- -------------------------- --- McDonald Roland Ronald, McDonald 16 Apathetic Thug Apathetic, Thug 15 In this case, the keys are of different lengths. It is possible to get away with this for a while, as I have found, but it's not perpetual and it's certainly not good programming practice. Anyway, the bottom line here is that you want to: INDEX ON STR(trans->account_no) + DTOC(SUBSTR(trans->date, 7, 2)) + ; DTOC(SUBSTR(trans->date, 1, 2)) + DTOC(SUBSTR(trans->date, 4, 2)) This will pad account_no's with leading spaces which will force correct indexing in the situation: 1023 vs. 205 because " 205" < "1023" And secondly, because the date will now be in a sortable order. There's something you must understand about dates. dBASE will take care of them quite nicly, manipulatively, while they're of type DATE. As soon as you convert them to a string with DTOC(), they are treated exactly as a string and the year doesn't take precedence in sorting unless listed explicitly first as in the above index expression. I hope this discussion has hslped. If you have any more problems, mail your questions to me if you'd like. UUCP: {cbosgd hplabs!hp-sdd sdcsvax nosc}!crash!pnet01!benh ARPA: crash!pnet01!benh@nosc.mil INET: benh@pnet01.cts.com