Xref: utzoo comp.sys.ibm.pc:15971 comp.databases:1044
Path: utzoo!attcan!uunet!lll-winken!lll-lcc!ames!ll-xn!mit-eddie!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: Huh?  I *tried it just before posting on Foxbase +*
Message-ID: <1114@ddsw1.UUCP>
Date: 27 May 88 02:53:00 GMT
References: <3026@crash.cts.com>
Reply-To: karl@ddsw1.UUCP (Karl Denninger)
Organization: Macro Computer Solutions, Inc., Mundelein, IL
Lines: 78

In article <3026@crash.cts.com> benh@pnet01.cts.com (B. Humphreys) writes:
>In article <529@etn-rad.UUCP> markus@etn-rad.UUCP (Markus Richardson) writes:

>>>[Actual example deleted]

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

Nope - remember, the original posted had a couple of OTHER lines that
you left out (a Set 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?

(Account number, yes -- but the example holds regardless of the name of the
numeric field in question.  The only important thing is that the field in
question is of numeric type)

It works as posted if you had used SET DATE ANSI (as the poster specified he 
did).  I tried this out before posting on Foxbase+ (A Dbase III+ near-exact 
clone) and it works fine.  "SET DATE ANSI" is one of those little-used 
options, but it is quite useful for this sort of stuff....

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

>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:

This will work, the gyrations with the date are unnecessary.

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

True as far as you went -- but the conversion is done according the the 
format you have specified with the SET DATE option.  Dbase III and clones
store dates internally as a binary pattern -- probably a 32-bit value offset
from some "base" date (or even 00/00/00).  When you display a date-format
variable (or field) it's converted on the fly according to the method you've
specified.  It just happens that the default is "US" format.

Note that the side effect of using this "set date" option is that *ALL* date
strings will be displayed and manipulated in "ANSI" format.  We never include 
format strings with our date print-outs, so I have no idea what happens if 
you try to force it to display with the "/"s instead...  If you (and your 
customers) like the ANSI format then it's great, and kills two birds with 
one stone.

Also, beware of switching modes if you have an active index built in the 
other mode!  That one I also haven't tried and it seems like the results 
could be rather scary.

---
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"