Thanks, Reason I ended up trimming, upper, etc. was this come from a dol spreadsheet, when went to a open office calc spreadsheet which saved to a dbf table, at which point I processed it, and now I'm converting it to a SQL insert statement that I can putty to mysql on a small orange host which at that point, I can then begin working on the google maps code .
Lot of steps, and I'm on a deadline so I guess I'm getting paranoid in my old age. Was going to do it in php to save a few steps but when I couldn't figure out how to quickly do a scan, do while, I said VFP to the rescue -----Original Message----- From: ProFox [mailto:[email protected]] On Behalf Of Richard Kaye Sent: Wednesday, January 21, 2015 1:25 PM To: [email protected] Subject: RE: boy, I'm rusty Sticking that comma in your search string may have unintended consequences. I know it was a one time and you're done but I just can't help myself... :-) I would dump all that trimming; city and state columns usually aren't so wide that you need to worry about storage in the DB or the overall width of the index tag. Also, if you are using the UPPER function in your SEEK or LOCATE or INDEXSEEK, Rushmore will only kick in if your index is built using the UPPER function. You can also put a scope on your SCAN. ...untested pseudo-code ... do setup stuff here (i.e. use tables, etc.) SELECT mastfile SCAN WHILE INDEXSEEK(UPPER(mastfile.mcity) + UPPER(mastfile.mstate),.t.,[cities],[city]) REPLACE lat WITH cities.n3 IN mastfile REPLACE lng WITH cities.n4 IN mastfile ENDSCAN -- rk -----Original Message----- From: ProfoxTech [mailto:[email protected]] On Behalf Of Virgil Bierschwale Sent: Wednesday, January 21, 2015 12:46 PM To: [email protected] Subject: Re: boy, I'm rusty that is what was happening to me SAN MARCOSTX was killing it This is what I ended up with. I didn't change the city index tag, although I probably should have taken out the rtrim() in the index. This was just a quickie one time deal SELECT 0 USE h-1b_fy14_q4 ALIAS mastfile SELECT 0 USE us_cities ALIAS cities SET ORDER TO city SELECT mastfile GO top DO WHILE .not. EOF() mcity = RTRIM(UPPER(lca_case12)) mstate = RTRIM(UPPER(lca_case13)) SELECT cities SEEK mcity + ", " + mstate IF FOUND() * found it mlat = n3 mlng = n4 SELECT mastfile replace lat WITH mlat replace lng WITH mlng ENDIF SELECT mastfile SKIP ENDDO RETURN On Wed, Jan 21, 2015 at 11:30 AM, Tracy Pearson <[email protected]> wrote: > John Weller wrote on 2015-01-21: > > Better to use IF SEEK(mcity+mstate, 'Cities', 'City'). > > Mcity+mstate > must > > match index so need to be padded to same length as the index > > fields, I wouldn't index on a trimmed field, others may disagree. > > Also, suggest > SCAN > > ENDSCAN better than DO WHILE. > > > > Try: > > > > USE h-1b_fy14_q4 ALIAS mastfile in 0 USE us_cities ALIAS cities in > > 0 SELECT Mastfile SCAN > > IF SEEK(mcity+mstate, 'Cities', 'City') > > mlat = n3 > > mlng = n4 > > ENDIF > > ENDSCAN > > > > Regards > > > > John > > > > I agree with not using trims in indexes. > >From what I've seen, if your data is similar to > Fargo, ND > Minneapolis, MN > > Your index may be built like > FargoND > MinneMN > > I've actually seen VFP 9 error with a bad index when searching for > Minneapolis. > > And in John's example, setting mlat and mlng will need Cities.n3 and > Cities.n4 respectfully. > > > > Tracy Pearson > PowerChurch Software > > [excessive quoting removed by server] _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[email protected] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
