== and = and set exac on/off and alltrim() 1

[shumi99]

Hi,

I've just noticed something in my queries.

Even if I have set exac on and so a alltrim() of my field, I don't get the same result as if I do a "==". any ideas why?

any simple little fix?

please help because I don't feel like going thru all my code and fixxing it.

thanks,

 

[cbasoz]

First there is nothing to fix. All you need isto understand how =, == and set exact/ansi works.

You are saying queries, if you mean SQL then set exact has no effect on SQL. Instead you need SET ANSI. In an SQL regardless of ANSI setting:

==

mean exactly equal (trailing spaces are ignored from either side). Since trailing spaces have no importance, if you are doing AllTrim() to trim from right then it is useless. Already implicitly RTrim()med for comparison.

If you meant xbase queries like a locate, seek etc then SET EXACT has effect and == behavior is different. In this case:

==

again means exactly equal BUT it trailing spaces are not trimmed and the match must be exact including the trailing space.

Once you understand this it is easy to write correct query.

PS: With defaults in VFP = makes a comparison up to the lenght of right string. ie:

"Hello World" = ""
"Hello World" = "Hello"
"Hello World" = "H"
"Hello World" = "Hello World"


Cetin Basoz
MS Foxpro MVP, MCP

 

[shumi99]

Hi Cetin,

Yes, I am referring to SQL queries (SELE * FROM... WHERE....ETC.).

OK, so what you are saying is just "SET ANSI ON" and that fixes everything, yes?

Ahhhh, phew! LOL.... I knew there was a quick fix.


Many many thanks.

 

[shumi99]

Actually, I just went thru my code and it's not the "select" that's giving me the problem result. It's the "COPY TO ctyqry FOR cityn=per AND DTOC(cdate)=per1". I tried even with the "SET ANSI ON" and it still gives me the "bad" result. Should I use the "==" or is there any other workaround?

Please let me know.


Thanks.

 

[Mike Lewis]

Shumi,

As you've discovered, SET ANSI ON only applies to SQL statements, not to native VFP code (which includes COPY TO).

Regardless of the setting of SET ANSI or SET EXACT, you still need to ALLTRIM() the fields in order for the match to work the way you expect.

So, in your example, if City has trailing spaces and Per doesn't (or has a different number of trailing spaces), the match will fail. You would need to say:

ALLTRIM(City) = ALLTRIM(Per)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

That is xBase.
1) Use set exact on.
2) Do not skip prefixing your memory variables with m.
3) Do not use unnecessary and ambigious conversions of datatypes that might lead to false results(like you do with converting dtoc()).
4) Do not forget that VFP is case sensitive

ie:

set exact on
per  = "BOSTON"
per1 = date(2010,1,31)
COPY TO ctyqry FOR ;
  upper(cityn)=m.per AND ;
  cdate=m.per1

With string values, if you use "SET EXACT ON" you don't need to use alltrim() - again if you're doing it to trim trailing spaces.


Cetin Basoz
MS Foxpro MVP, MCP

 

[shumi99]

Nice. Problem fixed.

Also, for others, I would add in a 5th item to Cetin's list:

5) Make sure your REPO has enough space for the whole field name (and then some).

I'm going to admit that the problem was actually the repo.

My report was just showing "Netherlands Antilles" when the field value was actally "Netherlands Antilles Cellular". There wasn't enough space for the word "Cellular" therefore it was really causing a lot of confusion with "Netherlands Antilles". Anyhow, it's fixed. Thanks to all.