Secondairy Index on multiple fields in Paradox 1

[RtWee]

Hi

I've got paradox tables containing three colums with x y and z coordinates.
I want to select records in a certain area
X +/- dx
Y +/- dy
Z +/- dz

for this I've made a secondairy index with the index fields x,y,z

XYZtable.AddIndex('secXYZ','X;Y;Z',[ixCaseInsensitive]);

the index is there, I've checked
but if I want to use it

IndexName:='secxyz'; SetRange([x-dx,y-dy,z-dz],[x+dx,y+dy,z+dz]);

the index only seems to work on the x-coordinate and not all three of them.

Any thought of what I may do wrong?

I don't know if it matters but the primary index is also on the X,Y and Z and X,Y and Z each has it's own secondary index

 

[towerbase]

Can you give the exact code that you call SetRange with?

 

[RtWee]

Towerbase

What I have written in my question is the exact code:

IndexName:='secxyz'; SetRange([x-dx,y-dy,z-dz],[x+dx,y+dy,z+dz]);


I have also tried
IndexName :='secXYZ';
tblcluster.SetRange([200,300,900],[1000,1500,902]);
but again, the index only worked on the x

I have also tried the primary index which I have on the combination of Z-field, X-field, Y-field
this time the index only worked on the Z-field, which is the first one in the row.

It seems that the method in which I use Indexname and setrange it is not possible to use multiple fields.

I have bypassed the problem now by using a query that does the trick, but I am still curious what the correct format is.

Greetz

Rene

 

[towerbase]

But you must be setting the variables x, y, z, dx, dy, dz somehwere. Are you calling ApplyRange ?

What is the evidence that it is not working?

What version of Delphi (and Paradox table level) are you using?

Andrew

 

[RtWee]

Andrew

dxy:=StrToInt(Form1.edtxysearchsize.Text);
dz:=StrToint(Form1.edtZsearchsize.Text);

with table1 do
begin
Locate('Point',Point1,[]);
xx:=fieldbyname('x').asinteger;
yy:=fieldbyname('y').asinteger;
zz:=fieldbyname('z').asinteger;
IndexName:='secxyz';
SetRange([xx-dxy,yy-dxy,zz-dz],[xx+dxy,yy+dxy,zz+dz]);
end;

I haven't tried Applyrange because I have never used when applying a range with one field, but I'll give it a go.

The evidence is that my results did not make sense so I followed my code step by step and saw that the range is only applied on the x-field

I use Delphi 6 and Paradox 8


Rene

 

[RtWee]

Andrew

No change using ApplyRange

(was a good suggestion though)

 

[towerbase]

My mistake, there is no need to call ApplyRange because SetRange includes it.

I think that the problem is a misunderstanding about ranges.

A range of records is a contiguous block of records in the dataset. You specify the first record in the block and the last record in the block. I think you are trying to specify a number of blocks in the dataset and that cannot be done with ranges. You should use a filter instead.

So for example if your dataset consisted of records as follows (these are the x, y and z values):

1, 2, 1
1, 2, 2
1, 3, 1
1, 3, 2
1, 3, 3
1, 3, 4
2, 1, 1
2, 1, 2
2, 2, 1
2, 2, 2
3, 1, 1

you could specify the first record in the range as being 1,2,2 and the last record in the range as being 2,2,2 but this will include records such as 1,3,4 which you would not want.

I hope this helps and explains why ranges are not appropriate to your requirement.

Andrew

 

[RtWee]

Well your explanation is conforming my experience

I’ve used a query to solve my problem
Query1.Active := false;
SQL.Clear;
sql.Add('SELECT DISTINCT Z, X, Y');
sql.Add('FROM "'+ Table1.TableName +'"');
sql.Add('WHERE');
sql.Add('(Z > ' + IntToStr(zz-dz)+'.0)');
sql.Add('AND (Z < ' + IntToStr(zz+dz)+'.0)');
sql.Add('AND (X > ' + IntToStr(xx-dxy)+'.0)');
sql.Add('AND (X < ' + IntToStr(xx+dxy)+'.0)');
sql.Add('AND (Y > ' + IntToStr(yy-dxy)+'.0)');
sql.Add('AND (Y < ' + IntToStr(yy+dxy)+'.0)');
sql.Add('ORDER BY Z, X, Y');
Query1.Active := true;

And it works

Thank you for your time

Rene

 

[towerbase]

Glad to be of help.

I think you're right, SQL is probably better than using a filter.

Good luck.

Andrew