3

I have a problem with setting a range on a secondary index in a Paradox 7 table using Delphi2010.

The relevant fields are:

FeatureType (int); YMax (int); XMax (int); YMin (int); Xmin (int). The secondary index contains all these fields in this order.

I tested using a SetRange statement like so (not necessary to add all field values, rest is assumed NULL and all values are included):

table1.IndexName := 'YMaxIndex';
table1.SetRange([101, 280110400],[101, 285103294]); //386236 records

And tried to get a 0 result by adding to the constraints:

table1.IndexName := 'YMaxIndex';
table1.SetRange([101, 280110400, 1],[101, 285103294, 1]); //386236 records

But still gets 3863236, which is clearly incorrect when checking the values in the XMax field in the table.

Can someone please explain to me what I am not understanding about Paradox index and SetRange? I have used similar code frequently but not necessarily with 3 fields specifying the range.

Update

See Uwe's response below. The final code solution follows (new ranges for XMax):

Table1.SetRange([101,280110400], [101,285103294]);
Table1.Filter := 'XMax > 100000 and XMax < 110000';
Table1.Filtered := true;
bosvos
  • 549
  • 5
  • 14

3 Answers3

4

An index range is always taken as a whole over all fields and not looking for each field individually. The result set will contain every record that is in between those ranges. The comparison is made for each index field in the given order.

In your case it will check if the record's FeatureType lies in between 101..101. If the field contains 101 it is taken into consideration. As the field value lies at the border of the range, the next fields are checked.

If the YMax field lies in between 280110400..285103294 and the value doesn't match the borders (280110400 or 285103294), it is taken into the result set without any further checking. In that case the remaining index fields are not checked.

The result you are trying to get is only possible with a filter condition - or with an appropriate SQL Select clause.

Uwe Raabe
  • 45,288
  • 3
  • 82
  • 130
  • Thanks, I'd forgotten about this small detail! I've tested your suggestion with the filter and that solves my problem...now for benchmarking. – bosvos May 27 '11 at 09:00
2

for range set with table1.SetRange([101, 280110400, 1],[101, 285103294, 1]); Folow values are in range

  • 101 280110400 1
  • 101 280110400 2
  • 101 280110400 3
  • ....
  • 101 280110401 -maxint
  • ....
  • 101 280110401 maxint
  • ....
  • 101 285103294 0
  • 101 285103294 1
tico
  • 124
  • 1
  • 6
  • are you saying that the 3rd value (the one for XMax) is just ignored? Please explain more. The result you posted would be equivalent to table1.SetRange([101, 280110400],[101, 285103294]); – bosvos May 27 '11 at 07:54
  • After having read the other posts, I now understand your list. Thanks for posting the example. – bosvos May 27 '11 at 09:10
2

A little clarification to the previous answers:

SetRange checks separately the range start and end conditions, for example we have

SetRange([1,2], [2,2])

and record (1, 3);

Range start: we have 1 = 1 for the first field (boundary), so we check the second field (2 < 3) - the range start condition is satisfied.

Range end: we have 1 < 2 for the first field (not boundary), so the second field is not checked - the range end condition is satisfied.

The record is in range.

kludg
  • 27,213
  • 5
  • 67
  • 118