Any ideas on using locate or something equally fast in an existing result set to find the nearest (as opposed to a partially matching) value?

search = 'JOHNS' in a result containing

JONES
JOHNVILLE
SMITH

I would like to land on johnville because nothing exactly matches.

Erm, restated for accuracy,

search = 'JOHNSTON' in a result containing

JOHNSON
JOHNSTONE
SMITH

I would like to land on JOHNSTONE because nothing exactly matches.

Jeff

>Any ideas on using locate or something equally fast in an existing result set to find the nearest (as opposed to a partially matching) value?
>
>search = 'JOHNS' in a result containing
>
>JONES
>JOHNVILLE
>SMITH
>
>I would like to land on johnville because nothing exactly matches.

You're looking for the equivalent of the table.FindNearest I think. That wouldn't work either if the documentation is anything to go by. The best I can suggest is something like

procedure TForm1.Button4Click(Sender: TObject);
var
bumph: string;
Found: boolean;
begin
bumph := 'arch@j';
Found := False;
while not Found do begin
 if Length(bumph) = 1 then Found := True else begin
  if emails.Locate('_emdestination', bumph, [loPartialKey]) then begin
   if Copy(emails.FieldByName('_emdestination').AsString, 1, Length(bumph)) = bumph
    then Found := True
   else Bumph := Copy(Bumph, 1, Length(Bumph) - 1);
  end else Bumph := Copy(Bumph, 1, Length(Bumph) - 1);
 end;
end;
showmessage(emails.FieldByName('_emdestination').AsString + '   ' + bumph);
end;

Roy Lambert [Team Elevate]

Yeah I took that approach, but it can get expensive for objects with events hanging off of them.

Tim, that would be a tres plus sexy feature!!! Build it into TEDBQuery, how hard could it be? (joke, son) And our search forms would absolutely sing with the interface I have in mind.

A reasonable workaround is to construct a query such as

select lastname, firstname
from doctor
where lastname + ',' + firstname >= 'Jones,Z'
order by lastname, firstname

And then locate on what it finds. An extra trip to the server to find the locate that will work is far cheaper than hitting several locates on an open result set and is easier to implement correct results across multiple columns.

Jeff

>Yeah I took that approach, but it can get expensive for objects with events hanging off of them.

True - so add a disablecontrols/enablecontrols in

>Tim, that would be a tres plus sexy feature!!! Build it into TEDBQuery, how hard could it be? (joke, son) And our search forms would absolutely sing with the interface I have in mind.

The problem with doing anything is the lack of indices. I have two main approaches when I need to do much with the results of a query 1) using in-memory tables to hold the result set 2) use TMS AdvStringGrid to hold the output. Depending on what you want to do, the size of the result set, wether you're using c/s or f/s (in the former case in-memory tables anint much good cos they sit on the server) it can be very efficient, especially when you have an insensitive result set.

Roy Lambert [Team Elevate]

Jeff

>A reasonable workaround is to construct a query such as
>
>select lastname, firstname
>from doctor
>where lastname + ',' + firstname >= 'Jones,Z'
>order by lastname, firstname
>
>And then locate on what it finds. An extra trip to the server to find the locate that will work is far cheaper than hitting several locates on an open result set and is easier to implement correct results across multiple columns.

Good idea as long as you can pick the right lastname I do something similar on the contacts form of my app for when people type in a name (Contacts is a table not a query though)

procedure TContactsForm.DoFindTimer(Sender: TObject);
begin
DoFind.Enabled := False;
if (Finder.Text <> '') and (Finder.Text[Length(Finder.Text)] <> ',') then begin
 if Finder.Text[1] in ['0'..'9'] then begin
  Contacts.Locate('_ID', StrToInt(Finder.Text), []);
 end else begin
  if (0 = Pos(',', Finder.Text)) then begin
   Contacts.Locate('_Surname', Finder.Text, [loCaseInsensitive, loPartialKey]);
  end else begin
   if not Contacts.Locate('_Surname;_Forename', VarArrayOf([SubFld(Finder.Text, ',', 1), SubFld(Finder.Text, ',', 2)]), [loCaseInsensitive, loPartialKey]) then begin
    ComplexFind.Close;
     if not ComplexFind.Prepared then ComplexFind.Prepare;
    ComplexFind.ParamByName('Surname').AsString := SubFld(Finder.Text, ',', 1) + '%';
    ComplexFind.ParamByName('Forename').AsString := SubFld(Finder.Text, ',', 2) + '%';
    ComplexFind.ExecSQL;
    if ComplexFind.RecordCount > 0 then begin
     ComplexFind.First;
     Contacts.Locate('_ID', ComplexFind.Fields[0].AsInteger, []);
    end;
    ComplexFind.Close;
   end;
  end;
 end;
end;
end;

As you can guess with the function name its run off a timer which is reset every time a character is typed.

ComplexFind is

SELECT _ID ,_Surname,_Forename FROM Contacts
WHERE
_Surname LIKE :Surname
AND
_Forename LIKE :Forename
ORDER BY _Surname,_Forename

Its running in f/s mode so probably not as efficient as c/s but its pretty fast.

Jeff,

<< Any ideas on using locate or something equally fast in an existing result
set to find the nearest (as opposed to a partially matching) value? >>

You should be able to just use the >= operator combined with a RANGE 1 to 1.
You only want the next matching value, correct ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Ah good point, to protect against the index not existing.