danielg@uncmed.med.unc.edu (07/28/89)
Dear dBASE friends,
I have a problem to which I *know* there is a simple answer. I
have to locate a record in my database by two fields. I have
tried SET FILTER TO the first, then using a FIND command to
pinpoint my record, but as you might expect, this is ** much too
slow ***.
Please post, or e-mail to me.
Thanks.
Disclaimer: My opinion means | 'If you only knew how much I was holding
nothing, but His means | back, you would commend me'
everything. | - Charles Spurgeon, 19th century
danielg@uncmed.med.unc.edu | evangelist (on humor in preaching)englandr@phoenix.Princeton.EDU (Scott Englander) (07/28/89)
In article <472@uncmed.med.unc.edu> danielg@uncmed.med.unc.edu () writes: > I have a problem to which I *know* there is a simple answer. I > have to locate a record in my database by two fields. I have > tried SET FILTER TO the first, then using a FIND command to > pinpoint my record, but as you might expect, this is ** much too > slow ***. Let's say you want the record where field1 = x and field2 = y, where x and y are expressions appropriate to the respective field type. Then all you need to do is LOCATE FOR field1 = x .and. field2 = y This might still take a while, but should be faster than SET FILTER TO. Hope this helps. -- - Scott
jbrown@herron.uucp (Jordan Brown) (07/31/89)
In article <9636@phoenix.Princeton.EDU>, englandr@phoenix.Princeton.EDU (Scott Englander) writes: > Let's say you want the record where field1 = x and field2 = y, where x > and y are expressions appropriate to the respective field type. Then > all you need to do is > > LOCATE FOR field1 = x .and. field2 = y > > This might still take a while, but should be faster than SET FILTER TO. > Hope this helps. It shouldn't be any faster. It should be exactly the same speed. The real answer is to index on the pair of fields and seek on the concatenation. index on last+first to lf seek "Brown Jordan" if you don't have an such an index and don't want to maintain it, oh well, you're going to do a linear search. This won't be fun using either SET FILTER or LOCATE.
emuleomo@accur8.UUCP (Olumide Emuleomo) (08/01/89)
In article <472@uncmed.med.unc.edu>, danielg@uncmed.med.unc.edu writes: > Dear dBASE friends, > > I have a problem to which I *know* there is a simple answer. I > have to locate a record in my database by two fields. I have > tried SET FILTER TO the first, then using a FIND command to > pinpoint my record, but as you might expect, this is ** much too > slow ***. > > Please post, or e-mail to me. > > Thanks. Luckily, this is a simple problem. All you have to do is Index your DBF on one of the flds, say fld1 and write the following code.... USE <mydbf> INDEX idx1 SET EXACT ON SEEK value_1 && This searches the index on fld1 IF .NOT. FOUND() RETURN(.F.) && Since both flds have to match, no match is now possible! ENDIF DO WHILE fld_1 = value_1 .AND. .NOT. EOF() IF fld_2 = value_2 RETURN(.T.) ENDIF SKIP ENDDO RETURN(.F.) && Value not found!! Off course, you may need to CLOSE your DBF and etc... but the above code should return .T. if both values are in your DBF. If you are using dBASE III+ that does not have UDFs then pass a parameter to the above and set the value to .T. or .F. as needed. You may then test it in the calling pgm. GOOD LUCK Emuleomo O O (emuleomo@accur8@uunet.uu.net)
mr@cica.cica.indiana.edu (Michael Regoli) (08/01/89)
jbrown@herron.uucp (Jordan Brown) writes: >The real answer is to index on the pair of fields and seek on the >concatenation. >index on last+first to lf >seek "Brown Jordan" ^^^^^^^^^^^ why is there whitespace between first and last names? is this a tab? -- michael regoli mr@cica.indiana.edu regoli@iubacs.bitnet ...rutgers!iuvax!cica!mr
jbrown@herron.uucp (Jordan Brown) (08/02/89)
mr@cica.cica.indiana.edu (Michael Regoli) writes: > jbrown@herron.uucp (Jordan Brown) writes: > > >The real answer is to index on the pair of fields and seek on the > >concatenation. > > >index on last+first to lf > >seek "Brown Jordan" > ^^^^^^^^^^^ > why is there whitespace between first and last names? is this a > tab? I wondered if I should explain that further. Remember that dBASE (and most dBASEoids) uses fixed-width fields. The "last+first" that you're indexing on will include the trailing spaces from the "last" field (and those from the "first" field, but those are less important here). When you go to do the seek, you have to build a key that matches this pattern. (Typically you would do this by having the GET be that long when you ask the user for the value.) My example has a (um, 1 2 3 ... 16) 16 character "last" field. The indexing code in dBASE doesn't "know" that you're playing with two fields. It just sees one long string. When you do the seek, you better give it a string that looks similar.
timk@xenitec.uucp (Tim Kuehn) (08/02/89)
In article <61@cica.cica.indiana.edu> mr@cica.cica.indiana.edu (Michael Regoli) writes: >jbrown@herron.uucp (Jordan Brown) writes: >>index on last+first to lf >>seek "Brown Jordan" > ^^^^^^^^^^^ >why is there whitespace between first and last names? is this a >tab? Each record in a fixed-field size database such as implimented by dbase, if not filled by characters is "padded" on the right side by blanks, which is where the spaces between "Brown" and "Jordan" come from. >michael regoli >mr@cica.indiana.edu >regoli@iubacs.bitnet >...rutgers!iuvax!cica!mr +-----------------------------------------------------------------------------+ |Timothy D. Kuehn timk@xenitec | |TDK Consulting Services !watmath!xenitec!timk | |871 Victoria St. North, Suite 217A | |Kitchener, Ontario, Canada N2B 3S4 (519)-741-3623 | |DOS/Xenix - SW/HW. uC, uP, DBMS. Satisfaction Gauranteed| +-----------------------------------------------------------------------------+