Board index » delphi » Getting SetRange to work properly

Getting SetRange to work properly

Hi all,

I'm using Delphi 2 with a DBas IV app that is driving me crazy with a
problem using SetRange.

I'm using a table with the following Index

STR(EMPLOYEEID)+STR(TIMEIN)

where EmployeeID is a numeric 20, 0 field and TimeIn is a numeric 20, 8
field

EmployeeID is a foreign key, timeIn is a date and time stored as a
floating point number

with ds.Dataset as TTable do begin
               {Set the index to the one above...}
              If IndexName<>LOG_EMP_IDX then IndexName:=LOG_EMP_IDX;
              CancelRange;
              SetRangeStart;
              FieldByName('EmployeeID').asInteger:=Integer(Node.Data);
                //start date / time (as fp)
              FieldByName('TimeIn').asFloat:=FFilterDates[standard,
STARTDATE];               SetRangeEnd;
              FieldByName('EmployeeID').asInteger:=Integer(Node.Data);
                //End  date / time (as fp)
              FieldByName('TimeIn').asFloat:=FFilterDates[standard,
DT_ENDDATE];
              ApplyRange;
end;
 This basically works, except the returned timeIn values exceed the
range limiters.  Eg, if I ask for a single day say 36980.00 to 36981.00,
I will usually also get records in the range 36979.nn, 36981.nn returned
also.  Running a query or filter based on the parameters listed above,
results in the expected records being returned.

I think this has something to day with the way the composite indexes are
handled by the dbase format.  Does anyone know of a way to set up the
composite index so it will work correctly with setrange?

Thanks   Jeff

 

Re:Getting SetRange to work properly


Quote
>I'm using a table with the following Index

>STR(EMPLOYEEID)+STR(TIMEIN)

1. Do all your employee ids have the same numer of significant digits?
2. Seems to me that you need to insure that the second operand creates
strings of the same length, and thus you may have to pad some of them with
leading zeros.

Re:Getting SetRange to work properly


I don't do much work with dBase tables, so I may be way off base here, but
if I understand correctly how the index is constructed it will not organize
the data "properly", i.e. employee 1 records will be followed by employee 10
records, then employee 2 records.

It is puzzling that the range seems to be out by 1 around the TimeIn, and I
wonder if this is more a function of the data you are looking at rather than
the behaviour.  Have you tried an index with just str (TimeIn) to see how it
works?

I really think that what you need to do is have an index expression that
will construct keys that look like (for employee #9)

0000090036000.000000001

Quote
Jeff Brown wrote in message <37121030.311E8...@m130.aone.net.au>...
>Thanks for reply, to answer

>1/ unfortunately no.  EmployeeID can be from 1 to whatever the field size
can
>hold (in practice however, this is only up to say, 999 max.)
>2/ tried the LEFT... statement in the index originally, but in general this
>seemed to{*word*222}things up even more in situations other than this one.

>I think the problem either relates to the variable length of EmployeeID or
the
>converted fractional component of the TimeIn value.  What really has me
>confused though, is why it sort of ranges, but is out by approx 1 either
side
>of  the TimeIn range which are fp numbers with values of around 36000 (i.e
>dates & times).

>I sometimes wish I didn't dislike Paradox tables so much.

Re:Getting SetRange to work properly


Thanks for reply, to answer

1/ unfortunately no.  EmployeeID can be from 1 to whatever the field size can
hold (in practice however, this is only up to say, 999 max.)
2/ tried the LEFT... statement in the index originally, but in general this
seemed to{*word*222}things up even more in situations other than this one.

I think the problem either relates to the variable length of EmployeeID or the
converted fractional component of the TimeIn value.  What really has me
confused though, is why it sort of ranges, but is out by approx 1 either side
of  the TimeIn range which are fp numbers with values of around 36000 (i.e
dates & times).

I sometimes wish I didn't dislike Paradox tables so much.

Quote
Bruce Roberts wrote:
> >I'm using a table with the following Index

> >STR(EMPLOYEEID)+STR(TIMEIN)

> 1. Do all your employee ids have the same numer of significant digits?
> 2. Seems to me that you need to insure that the second operand creates
> strings of the same length, and thus you may have to pad some of them with
> leading zeros.

Re:Getting SetRange to work properly


Hmm, that got me thinking, as I had never actually looked at the table when
indexed with the problem index, but without any range or filtering set.  Quickly
set up an app to view all records with this index, and all employeeIDs are in
correct order.

Also, my main index is actually a simple index on the TimeIn field, and it
ranges as expected (The employeeID, TimeIn index is a "zoom" type function in
the main app, allowing the user to view the entries for a selected employee
only).

The darn thing works ok if I include a filter with or without a range, but this
table has typically many thousands of records in it and slows to a crawl when
filtered.

Got me beat.

Regards, Jeff

Quote
Bruce Roberts wrote:
> <snip>

Other Threads