Board index » delphi » Table.FindNearest doesn't appear to work with NULL values

Table.FindNearest doesn't appear to work with NULL values

How can I get FindNearest to work when the first key value of a
compound key is NULL?

If I try

Table1.FindNearest([NULL, 'def']);
or
Table1.FindNearest(['', 'def']);
or
Table1.FindNearest([' ', 'def']);

it will erroneously find the first record whose first key value is
not-null, like ['abc','any'] and not the record with ['','def'].

Apparently FindNearest does not like null key field values. Is there
any way around this?

TIA

 

Re:Table.FindNearest doesn't appear to work with NULL values


Hi Barry,

My first-ever Delphi project, I had terrible trouble with Table.FindNearest - results at any given time appeared to depend on the settings of various properties, apparently including whether there was an 'r' in the month and whether Uranus was in Libra. :)

Try using Table.SetRange first: this and a consequent rearrangement of code sorted all my problems (well, the Delphi ones anyway).

Hth.

Regards,

Stuart

Quote
Barry McClure <barry_mccl...@grebarsys.com> wrote:
>How can I get FindNearest to work when the first key value of a
>compound key is NULL?

>If I try

>Table1.FindNearest([NULL, 'def']);
>or
>Table1.FindNearest(['', 'def']);
>or
>Table1.FindNearest([' ', 'def']);

>it will erroneously find the first record whose first key value is
>not-null, like ['abc','any'] and not the record with ['','def'].

>Apparently FindNearest does not like null key field values. Is there
>any way around this?

>TIA

Re:Table.FindNearest doesn't appear to work with NULL values


Hi Barry,

My first-ever Delphi project, I had terrible trouble with
Table.FindNearest - results at any given time appeared to depend
on the settings of various properties, apparently including
whether there was an 'r' in the month and whether Uranus was in
Libra. :)

Try using Table.SetRange first: this and a consequent
rearrangement of code sorted all my problems (well, the Delphi
ones anyway).

Hth.

Regards,

Stuart

Re:Table.FindNearest doesn't appear to work with NULL values


Quote
"Stuart" <stu...@telmar.co.uk> wrote:

:
:Hi Barry,
:
:My first-ever Delphi project, I had terrible trouble with
:Table.FindNearest - results at any given time appeared to depend
:on the settings of various properties, apparently including
:whether there was an 'r' in the month and whether Uranus was in
:Libra. :)
:
:Try using Table.SetRange first: this and a consequent
:rearrangement of code sorted all my problems (well, the Delphi
:ones anyway).
:
:
Stuart,
        Ah, a programmer's life is never so simple.<g>

        I need to go through the table getting all of the unique keys.
I start off at the first record of the file, get the key, increment
it, and a FindNearest gets the next key. I loop through the file
incrementing and doing a FindNearest. This works great. On a million
row table I can get 100 unique keys in 0.5 seconds. If I try an SQL
"Select Distinct.." it takes 6.5 minutes. So naturally I prefer my way
of doing it.

Like I said, this works great for numeric and string keys. Except when
the first field of a compound key is null. Then FindNearest doesn't
stop on any of these null fields at all. It starts searching at the
first non-null key field.

        So is there another way to get all distinct key fields from a
file? (It should be generic enough to work on any table, not just
Paradox/dBase.)

Barry...

Re:Table.FindNearest doesn't appear to work with NULL values


Hi Barry,

Mmm, you may have me. I must confess that, being in charge of the
database design means I don't permit null keys <g>.

Point taken about 'Select Distinct', which occurred to me also.
However, your requirement for a generic solution rang a long-
forgotten bell. My previous comments were somewhat facetious (No?
Gerraway!) and I recollect that I did not entirely rule out ODBC
(or the various database vendors' implementation thereof) as the
culprit for differing results from .FindNearest. In that
project's case, access to different databases had come about
through development, rather than any requirement for the finished
product. Is there a possibility that ODBC is involved anywhere in
database access (at least one vendor of my ken used (still uses?)
it to communicate between different bits of their software.

Alternatively, if you're iterating through the table, is there
any mileage in simply starting with a .FindFirst and continuing
with .FindNexts, storing and comparing as you go?

Regards,

Stuart

Quote
Barry McClure <barry_mccl...@grebarsys.com> wrote:
>Stuart,
>    Ah, a programmer's life is never so simple.<g>

>    I need to go through the table getting all of the unique keys.
>I start off at the first record of the file, get the key, increment
>it, and a FindNearest gets the next key. I loop through the file
>incrementing and doing a FindNearest. This works great. On a million
>row table I can get 100 unique keys in 0.5 seconds. If I try an SQL
>"Select Distinct.." it takes 6.5 minutes. So naturally I prefer my way
>of doing it.

>Like I said, this works great for numeric and string keys. Except when
>the first field of a compound key is null. Then FindNearest doesn't
>stop on any of these null fields at all. It starts searching at the
>first non-null key field.

>    So is there another way to get all distinct key fields from a
>file? (It should be generic enough to work on any table, not just
>Paradox/dBase.)

>Barry...

Re:Table.FindNearest doesn't appear to work with NULL values


Quote
"Stuart" <stu...@telmar.co.uk> wrote:

:
:Alternatively, if you're iterating through the table, is there
:any mileage in simply starting with a .FindFirst and continuing
:with .FindNexts, storing and comparing as you go?
:

That all depends on how much coffee you're will to drink as it loops
through 1 million row tables.<g> To find all unique values for a key
using FindNearest is blazingly fast ( 0.5 sec). Looping I'm afraid may
take 30 seconds or more and would create really slow down network
traffic.

Since in my particular case all I want to do is display a combo box
with all previously entered values for that particular field, I will
probably ignore displaying those values where the first key field is
null. There is only 1 instance of where I will be using it on a
compound key so I can probably get away without displaying those
values.

Now you might be asking why don't I just create a lookup table and as
people enter data, add every unique value into the lookup table. Good
question. Since there will be a lot of file importing going on, I
don't want to slow things down by maintaining an extra table for every
possible lookup. Since these fields had to be keys anyways,
FindNearest works 99% of the time quite well.

I just thought Table1.FindNearest([Null, 'abc']) should work since it
accepts NULL as a parameter just fine. Oh well. Live and learn I
guess.

Barry...

Other Threads