Exact match using INLIST()

Hi --

Using VFP9 + XPPro.

I tried to use the INLIST() function in an SQL SELECT statement to get
only certain information from a table. I used SET EXACT ON before
running the statement. However, this did not result to "exact"
matches.

For example, I asked for 'African American', but I received 'African
American', as well as 'African American Females', and 'African
American Males'. I finally had to redo the statement without the
INLIST() function and use the "==" convention.

Thanks in advance for any clarification or advice.

David

Rush
Tue Sep 11 12:45:36 PDT 2007

David wrote:
> Hi --
>
> Using VFP9 + XPPro.
>
> I tried to use the INLIST() function in an SQL SELECT statement to get
> only certain information from a table. I used SET EXACT ON before
> running the statement. However, this did not result to "exact"
> matches.
>
> For example, I asked for 'African American', but I received 'African
> American', as well as 'African American Females', and 'African
> American Males'. I finally had to redo the statement without the
> INLIST() function and use the "==" convention.
>
> Thanks in advance for any clarification or advice.
>
> David
>
>
See the docs for the SET ANSI ON|OFF command, which affects how the '='
operator works in SQL.

- Rush

TonySper
Tue Sep 11 12:44:43 PDT 2007

INLIST() returns true if it finds the match you ask for. You asked for
'African American' so it will return true any time it finds that in your
search regardless of what else is in the search field.
TonySper

"David" <David.Aman@dpsnc.net> wrote in message
news:1189539138.448711.172620@i38g2000prf.googlegroups.com...
> Hi --
>
> Using VFP9 + XPPro.
>
> I tried to use the INLIST() function in an SQL SELECT statement to get
> only certain information from a table. I used SET EXACT ON before
> running the statement. However, this did not result to "exact"
> matches.
>
> For example, I asked for 'African American', but I received 'African
> American', as well as 'African American Females', and 'African
> American Males'. I finally had to redo the statement without the
> INLIST() function and use the "==" convention.
>
> Thanks in advance for any clarification or advice.
>
> David
>

David
Tue Sep 11 17:53:48 PDT 2007

On Sep 11, 3:44 pm, "TonySper" <tsperd...@nospambellsouth.net> wrote:
> INLIST() returns true if it finds the match you ask for. You asked for
> 'African American' so it will return true any time it finds that in your
> search regardless of what else is in the search field.
> TonySper
>
> "David" <David.A...@dpsnc.net> wrote in message
>
> news:1189539138.448711.172620@i38g2000prf.googlegroups.com...
>
>
>
> > Hi --
>
> > Using VFP9 + XPPro.
>
> > I tried to use the INLIST() function in an SQL SELECT statement to get
> > only certain information from a table. I used SET EXACT ON before
> > running the statement. However, this did not result to "exact"
> > matches.
>
> > For example, I asked for 'African American', but I received 'African
> > American', as well as 'African American Females', and 'African
> > American Males'. I finally had to redo the statement without the
> > INLIST() function and use the "==" convention.
>
> > Thanks in advance for any clarification or advice.
>
> > David- Hide quoted text -
>
> - Show quoted text -

Thanks very much for the help!

David

Roger
Tue Sep 11 22:10:05 PDT 2007

In addition to Rush's advice re Set Ansi, the
VFP InList() function can be a little confusing
concerning string comparisons. When used in an SQL
statement, InList respects the Set Ansi setting
whereas on its own, it respects the Set Exact
setting.

To avoid this confusion (and to make your SQL
statements more portable) you can use SQL's
native IN comparison operator, eg...

Select * From mytable where myfield In ("apples","oranges")

-Roger

"David" <David.Aman@dpsnc.net> wrote in message
news:1189539138.448711.172620@i38g2000prf.googlegroups.com...
> Hi --
>
> Using VFP9 + XPPro.
>
> I tried to use the INLIST() function in an SQL SELECT statement to get
> only certain information from a table. I used SET EXACT ON before
> running the statement. However, this did not result to "exact"
> matches.
>
> For example, I asked for 'African American', but I received 'African
> American', as well as 'African American Females', and 'African
> American Males'. I finally had to redo the statement without the
> INLIST() function and use the "==" convention.
>
> Thanks in advance for any clarification or advice.
>
> David
>

David
Wed Sep 12 10:54:56 PDT 2007

On Sep 12, 1:10 am, "Roger Ansell" <no...@realemailaddress.net> wrote:
> In addition to Rush's advice re Set Ansi, the
> VFP InList() function can be a little confusing
> concerning string comparisons. When used in an SQL
> statement, InList respects the Set Ansi setting
> whereas on its own, it respects the Set Exact
> setting.
>
> To avoid this confusion (and to make your SQL
> statements more portable) you can use SQL's
> native IN comparison operator, eg...
>
> Select * From mytable where myfield In ("apples","oranges")
>
> -Roger
>
> "David" <David.A...@dpsnc.net> wrote in message
>
> news:1189539138.448711.172620@i38g2000prf.googlegroups.com...
>
>
>
> > Hi --
>
> > Using VFP9 + XPPro.
>
> > I tried to use the INLIST() function in an SQL SELECT statement to get
> > only certain information from a table. I used SET EXACT ON before
> > running the statement. However, this did not result to "exact"
> > matches.
>
> > For example, I asked for 'African American', but I received 'African
> > American', as well as 'African American Females', and 'African
> > American Males'. I finally had to redo the statement without the
> > INLIST() function and use the "==" convention.
>
> > Thanks in advance for any clarification or advice.
>
> > David- Hide quoted text -
>
> - Show quoted text -

Thank you, Roger. That is interesting. Are there other commonly used
VFP functions that have similar characteristics...behaving one way in
an SQL statement, but a little differently when used on its own?

Thanks,
David

Jack
Wed Sep 12 12:30:07 PDT 2007

On Wed, 12 Sep 2007 10:54:56 -0700, David <David.Aman@dpsnc.net>
wrote:

>On Sep 12, 1:10 am, "Roger Ansell" <no...@realemailaddress.net> wrote:
>> In addition to Rush's advice re Set Ansi, the
>> VFP InList() function can be a little confusing
>> concerning string comparisons. When used in an SQL
>> statement, InList respects the Set Ansi setting
>> whereas on its own, it respects the Set Exact
>> setting.
>>
>> To avoid this confusion (and to make your SQL
>> statements more portable) you can use SQL's
>> native IN comparison operator, eg...
>>
>> Select * From mytable where myfield In ("apples","oranges")
>>
>> -Roger
>>
>> "David" <David.A...@dpsnc.net> wrote in message
>>
>> news:1189539138.448711.172620@i38g2000prf.googlegroups.com...
>>
>>
>>
>> > Hi --
>>
>> > Using VFP9 + XPPro.
>>
>> > I tried to use the INLIST() function in an SQL SELECT statement to get
>> > only certain information from a table. I used SET EXACT ON before
>> > running the statement. However, this did not result to "exact"
>> > matches.
>>
>> > For example, I asked for 'African American', but I received 'African
>> > American', as well as 'African American Females', and 'African
>> > American Males'. I finally had to redo the statement without the
>> > INLIST() function and use the "==" convention.
>>
>> > Thanks in advance for any clarification or advice.
>>
>> > David- Hide quoted text -
>>
>> - Show quoted text -
>
>Thank you, Roger. That is interesting. Are there other commonly used
>VFP functions that have similar characteristics...behaving one way in
>an SQL statement, but a little differently when used on its own?

It's not that the statement behaves differently, but that string
equality behaves differently. Any VFP function (if there are any
others) that compares strings will show the same behavior.

Anders
Wed Sep 12 12:28:35 PDT 2007

This is a multi-part message in MIME format.

------=_NextPart_000_0025_01C7F583.E04EF3E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

There's been a change in the IN (value_set) clause in VFP9.
Quote from Help:
<<
SQL SELECT IN (Value_Set) Clause
In previous versions of Visual FoxPro, the IN (Value_Set) clause for the =
WHERE clause in the SQL SELECT command is mapped to INLIST( ) function. =
In the current release, Visual FoxPro might stop evaluating values and =
expressions in the Value_Set list when the first match is found. =
Therefore, if the IN clause is not Rushmore-optimized, you can improve =
performance by placing values most likely to match in the beginning of =
the Value_Set list. For more information, see the description for the IN =
clause in the SELECT - SQL Command topic and the INLIST( ) Function.>>

-Anders
------=_NextPart_000_0025_01C7F583.E04EF3E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.6000.16525" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2>There's been a change in the IN =
(value_set) clause=20
in VFP9.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>Quote from Help:</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>&lt;&lt;</FONT></DIV>
<H3 class=3DsubHeading xmlns=3D""><FONT face=3DArial size=3D2>SQL SELECT =
IN (Value_Set)=20
Clause</FONT></H3>
<DIV class=3DsubSection xmlns=3D"">
<P><FONT face=3DArial size=3D2>In previous versions of Visual FoxPro, =
the IN (<SPAN=20
class=3Dparameter>Value_Set</SPAN>) clause for the WHERE clause in the =
SQL SELECT=20
command is mapped to INLIST(&nbsp;) function. In the current release, =
Visual=20
FoxPro might stop evaluating values and expressions in the <SPAN=20
class=3Dparameter>Value_Set</SPAN> list when the first match is found. =
Therefore,=20
if the IN clause is not Rushmore-optimized, you can improve performance =
by=20
placing values most likely to match in the beginning of the <SPAN=20
class=3Dparameter>Value_Set</SPAN> list. For more information, see the =
description=20
for the IN clause in the <SPAN class=3DlinkTerms><A=20
href=3D"mk:@MSITStore:c:\program%20files\microsoft%20visual%20foxpro%209\=
dv_foxhelp.chm::/html/815f7265-4dfd-40b9-8f19-0673b5a48847.htm">SELECT=20
- SQL Command</A></SPAN> topic and the <SPAN class=3DlinkTerms><A=20
href=3D"mk:@MSITStore:c:\program%20files\microsoft%20visual%20foxpro%209\=
dv_foxhelp.chm::/html/b955d46c-7d65-49bf-9927-360c93951f46.htm">INLIST(=20
) Function</A></SPAN>.&gt;&gt;</FONT></P></DIV>
<DIV><FONT face=3DArial size=3D2>-Anders =
&nbsp;</FONT></DIV></BODY></HTML>

------=_NextPart_000_0025_01C7F583.E04EF3E0--