simple query returning erroneous results

I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL Server.

i am trying this query:

Select selected, lookupkey from RPCFGDTL ;
where lookupkey= "MATL" ;
INTO CURSOR list11

it returns this value for lookupkey;
"MATLBS"

according to the where clause, it should have not brought this value.

Any help would be appreciated.
Thanks,
Marty

Eric
Mon Oct 10 14:49:24 CDT 2005

This depends on the SET EXACT setting.

Compare:


SET EXACT OFF
?"MATLBS" = "MATL"

SET EXACT ON
?"MATLBS" = "MATL"

See VFP help on SET EXACT for details.
--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts
Microsoft MVP for Visual FoxPro

"Marty" <mbills@ttecht(removethis).com> wrote in message
news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
>I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
>Server.
>
> i am trying this query:
>
> Select selected, lookupkey from RPCFGDTL ;
> where lookupkey= "MATL" ;
> INTO CURSOR list11
>
> it returns this value for lookupkey;
> "MATLBS"
>
> according to the where clause, it should have not brought this value.
>
> Any help would be appreciated.
> Thanks,
> Marty

mbills
Mon Oct 10 15:01:06 CDT 2005

I put the set exact on and i checked it in the options form. it still brings
back the wrong data.


"Eric den Doop" wrote:

> This depends on the SET EXACT setting.
>
> Compare:
>
>
> SET EXACT OFF
> ?"MATLBS" = "MATL"
>
> SET EXACT ON
> ?"MATLBS" = "MATL"
>
> See VFP help on SET EXACT for details.
> --
> Eric den Doop
> www.foxite.com - The Home Of The Visual FoxPro Experts
> Microsoft MVP for Visual FoxPro
>
> "Marty" <mbills@ttecht(removethis).com> wrote in message
> news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
> >I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
> >Server.
> >
> > i am trying this query:
> >
> > Select selected, lookupkey from RPCFGDTL ;
> > where lookupkey= "MATL" ;
> > INTO CURSOR list11
> >
> > it returns this value for lookupkey;
> > "MATLBS"
> >
> > according to the where clause, it should have not brought this value.
> >
> > Any help would be appreciated.
> > Thanks,
> > Marty
>
>
>

Anders
Mon Oct 10 23:54:20 CDT 2005

Select selected, lookupkey from RPCFGDTL ;
where lookupkey== "MATL" ;
INTO CURSOR list11

WHERE lookupkey LIKE 'MATL'

If you SET ANSI ON then
WHERE lookupkey='MATL'
will not return 'MATLBS' , onlt 'MATL'

-Anders


"Marty" <mbills@ttecht(removethis).com> skrev i meddelandet
news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
>I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
>Server.
>
> i am trying this query:
>
> Select selected, lookupkey from RPCFGDTL ;
> where lookupkey= "MATL" ;
> INTO CURSOR list11
>
> it returns this value for lookupkey;
> "MATLBS"
>
> according to the where clause, it should have not brought this value.
>
> Any help would be appreciated.
> Thanks,
> Marty

mbills
Mon Oct 10 15:18:02 CDT 2005

that fixed it thanks alot...


"Anders" wrote:

> Select selected, lookupkey from RPCFGDTL ;
> where lookupkey== "MATL" ;
> INTO CURSOR list11
>
> WHERE lookupkey LIKE 'MATL'
>
> If you SET ANSI ON then
> WHERE lookupkey='MATL'
> will not return 'MATLBS' , onlt 'MATL'
>
> -Anders
>
>
> "Marty" <mbills@ttecht(removethis).com> skrev i meddelandet
> news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
> >I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
> >Server.
> >
> > i am trying this query:
> >
> > Select selected, lookupkey from RPCFGDTL ;
> > where lookupkey= "MATL" ;
> > INTO CURSOR list11
> >
> > it returns this value for lookupkey;
> > "MATLBS"
> >
> > according to the where clause, it should have not brought this value.
> >
> > Any help would be appreciated.
> > Thanks,
> > Marty
>
>
>

Eric
Mon Oct 10 15:53:06 CDT 2005

Hmmm my answer was wrong. SET EXACT does not apply to SQL syntax.. I see
that Anders already pointed you to SET ANSI. Thanks Anders!
--
Eric den Doop
www.foxite.com - The Home Of The Visual FoxPro Experts
Microsoft MVP for Visual FoxPro

"Marty" <mbills@ttecht(removethis).com> wrote in message
news:5E4726BB-379D-4B1A-B3AF-2BA272C28368@microsoft.com...
>I put the set exact on and i checked it in the options form. it still
>brings
> back the wrong data.
>
>
> "Eric den Doop" wrote:
>
>> This depends on the SET EXACT setting.
>>
>> Compare:
>>
>>
>> SET EXACT OFF
>> ?"MATLBS" = "MATL"
>>
>> SET EXACT ON
>> ?"MATLBS" = "MATL"
>>
>> See VFP help on SET EXACT for details.
>> --
>> Eric den Doop
>> www.foxite.com - The Home Of The Visual FoxPro Experts
>> Microsoft MVP for Visual FoxPro
>>
>> "Marty" <mbills@ttecht(removethis).com> wrote in message
>> news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
>> >I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
>> >Server.
>> >
>> > i am trying this query:
>> >
>> > Select selected, lookupkey from RPCFGDTL ;
>> > where lookupkey= "MATL" ;
>> > INTO CURSOR list11
>> >
>> > it returns this value for lookupkey;
>> > "MATLBS"
>> >
>> > according to the where clause, it should have not brought this value.
>> >
>> > Any help would be appreciated.
>> > Thanks,
>> > Marty
>>
>>
>>

Anders
Tue Oct 11 00:07:36 CDT 2005

Ho Eric.
SET EXACT doesn't affect SQL queries. SET ANSI does.

-Anders

"Eric den Doop" <ericdendoop@xspamblockxfoxite.com> skrev i meddelandet
news:OnY4NNdzFHA.3312@TK2MSFTNGP09.phx.gbl...
> This depends on the SET EXACT setting.
>
> Compare:
>
>
> SET EXACT OFF
> ?"MATLBS" = "MATL"
>
> SET EXACT ON
> ?"MATLBS" = "MATL"
>
> See VFP help on SET EXACT for details.
> --
> Eric den Doop
> www.foxite.com - The Home Of The Visual FoxPro Experts
> Microsoft MVP for Visual FoxPro
>
> "Marty" <mbills@ttecht(removethis).com> wrote in message
> news:246B291D-C971-4201-8E97-EA2F1367BE7E@microsoft.com...
>>I have a cursor named rpcfgdtl. it is from a Stored Procedure in SQL
>>Server.
>>
>> i am trying this query:
>>
>> Select selected, lookupkey from RPCFGDTL ;
>> where lookupkey= "MATL" ;
>> INTO CURSOR list11
>>
>> it returns this value for lookupkey;
>> "MATLBS"
>>
>> according to the where clause, it should have not brought this value.
>>
>> Any help would be appreciated.
>> Thanks,
>> Marty
>
>