Start a new topic

DDA Find issue

I have an index with three fields.

Find with partial match using first 2 fields will go to the first record found with field 1. Does not look at the second field at all.

This happens if both fields are not found in any records.

In my case it should look at both fields and if not found then return false.



1 Comment

I take it you mean that you have a single index defined with three columns, and you want to search for a match to specific values for the first two columns of the index regardless of the third column.

I think I know what's happening.  To search on just two of the three columns in the index you must be passing partialMatching = true, but that also allows it to match on just the first column.

You can still do what you want for this search with the DDA, but you should probably try using the SetScope method instead (this is what the internal optimizer uses for indexing), and I recommend using the overload which takes an IVistaDBRow argument rather than a string expression for the keys; it gives you more complete control over the key--especially when you aren't specifying a value for every column in the index.

Think of a multi-column index like a multi-digit number (where 0 will represent a null value which may (or may not) be allowed as a data value and 9 will represent a special max value that isn't a legal column value in the data but is allowed in the key).  You set the first and second digit to the values you want to find for them, such as 4 and 7.  To find the first occurrence of these values together, we have to bias the third column to its lowest possible key value (0, meaning null), so we get the key 470.  There is also the internal RowId value (which is unique to each row, in the order they were added to the table, so let's use a 4-digit number for the RowId in this example), so the key really needs to be 470.0000 (as opposed to 470.9999, which would skip over and exclude any possible matches for 0 (null) for the third column... or using any RowId value in between, which would include or exclude a match for 0 (null) for the third column depending on where the row falls in the original insert order--which just isn't a sensible criteria for this index search).

With the Find method (and passing partialMatching = true), you are only specifying the lower bound, and because you aren't specifying the third column and are specifying partialMatching = true, if the lowest number in the index with a value greater than or equal to 470.0000 starts with a 4 but not followed by a 7, then it will return that "partially-matching" row as "success".  I think you would find that an index row of 462.0264, for example, would not be found by your search because it is lower than the specified key value of 470.0000; only a row of 483.0076, for example, will be found... and only if there weren't any 47x.xxxx rows in the index.  The real problem is that it doesn't know your upper bound for "success".  You could use the SetScope method and specify the upper bound explicitly, or you can still use Find with only the lower bound and then perform your own explicit checking for whether the row "found" exceeds your intended upper bound--by testing the column values in the row it landed on.  (Or use a two-column index and partialMatching = false, but presumably that's less desirable.)

To use the SetScope approach we'll need the key for the upper bound, so we still need the 4 and 7, but we will bias the key to the end of the range:  479.9999.  So, we set the scope to be 470.0000 to 479.9999, and any rows in the index with values between them are a match to your desired criteria:  any value between 470 and 479 would be included.  With the ActiveIndex and a SetScope, you can then call the First() method and then test the EndOfTable property (and optionally walk the matches with Next() while continuing to test for EndOfTable).

For an example from .NET (C#):

table.ActiveIndex = "IX_TableName_Col1_Col2_Col3"; // ...or whatever your index is named.

var lowerKey = table.CurrentKey; // This will give you a copy from the current key for the current ActiveIndex.

var upperKey = table.CurrentKey; // And a separate instance copy to use for the upper bound.

Then you need to bias the keys (even when assigning all defined columns--it also sorts by the internal RowId value) and clear out the "current" values we didn't really want (just the easiest way to get a row with the right key schema).  The easiest way is:

lowerKey.InitTop(); // Reset the lower-bound key to the "top" (start) of the current ActiveIndex order.

upperKey.InitBottom(); // Reset the upper-bound key to the "bottom" (end) of the current ActiveIndex order.

And *then* set the column values that you want in each key.  You don't have to set all of them, but don't leave gaps from the left of the key or you won't get a sensible search.  (Searching for 073.0000 to 973.9999 will not match just the rows with 7 and 3 for the second and third columns!)  Any columns you don't set (and the RowId) will be left as the "bias" values from the InitTop() and InitBottom() calls.  You could also just set them all explicitly, but then you have to remember to do the opposite with any Descending columns and remember to set the RowId correctly, too.

lowerKey[0].Value = 4;

upperKey[0].Value = 4;

lowerKey[1].Value = 7;

upperKey[1].Value = 7;

table.SetScope(lowerKey, upperKey);


And now you can test table.EndOfTable and optionally loop over all matches.

Login to post a comment