![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 9 of 9 total |
![]() |
Mon, Jul 19 2010 8:34 PM | Permanent Link |
Jeff Dunlop | 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. |
Mon, Jul 19 2010 8:39 PM | Permanent Link |
Jeff Dunlop | Erm, restated for accuracy,
search = 'JOHNSTON' in a result containing JOHNSON JOHNSTONE SMITH I would like to land on JOHNSTONE because nothing exactly matches. |
Tue, Jul 20 2010 3:30 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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] |
Tue, Jul 20 2010 11:06 PM | Permanent Link |
Jeff Dunlop | 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. |
Wed, Jul 21 2010 12:08 AM | Permanent Link |
Jeff Dunlop | 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. |
Wed, Jul 21 2010 3:49 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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] |
Wed, Jul 21 2010 3:59 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 ![]() 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. |
Wed, Jul 21 2010 5:34 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Jul 21 2010 7:56 PM | Permanent Link |
Jeff Dunlop | Ah good point, to protect against the index not existing.
|
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |