Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Andy Kramek
  Where is Andy Kramek?
 Hot Springs Village
 Arkansas - United States
 Andy Kramek
 To: Keith Patterson
  Where is Keith Patterson?
 Roanoke
 Virginia - United States
 Keith Patterson
 Tags
Subject: RE: Query question
Thread ID: 228555 Message ID: 228567 # Views: 1 # Ratings: 1
Version: Visual FoxPro 6 Category: Databases, Tables and SQL Server
Date: Tuesday, May 19, 2009 10:51:10 PM         
   


> I am running this query
>
> select p_reason from hrpersnl where p_reason = 'PRN'
>
> and it is returning rows where p_reason = 'PRN', but it is also returning rows where p_reason = 'PRN2PT' and rows where p_reason = 'PRN2FT'.
>
> This is a FoxPro table (ABRA) and I'm not terribly familiar with Foxpro syntax, but I've not seen anything act like this before.
>
> Any ideas why I'm experiencing these strange query results?

The reason is that VFP's default behavior is to do what looks very like a case-Sensitive "LIKE xxx%" search (actually it is a little more complex than that, but the result is pretty much the same).

VFP has a couple of ways of limiting the result set; as Borislaav told you, you can use the "==" comparator to force an exact match but remember that it is still going to be case-sensitive so the best form for the query is actually:

WHERE UPPER( ALLTRIM( p_reason )) == 'PRN'

this will force an exact match on the letters "PRN", "pRN', "prn" etc etc

Regards
Andy Kramek
Microsoft MVP (Visual FoxPro)
Tightline Computers Inc, Akron Ohio, USA

ENTIRE THREAD

Query question Posted by Keith Patterson @ 5/19/2009 9:33:41 PM
RE: Query question Posted by Borislav Borissov @ 5/19/2009 9:41:49 PM
RE: Query question Posted by gerrit broekhuis @ 5/19/2009 9:44:27 PM
RE: Query question Posted by Borislav Borissov @ 5/19/2009 9:54:37 PM
RE: Query question Posted by Stefan Wuebbe @ 5/19/2009 10:06:03 PM
RE: Query question Posted by Andy Kramek @ 5/19/2009 10:51:10 PM
RE: Query question Posted by Keith Patterson @ 5/21/2009 6:45:01 PM
RE: Query question Posted by Anders Altberg @ 5/20/2009 12:10:37 AM