How many “and”? 3

[Mandy_crw]

Hi everyone… just wondering… i have a SELE and SCAN… in my code, just wanna ask how many AND can i use with these two?

SCAN FOR condition1 and condition2 and condition 3
ENDSCAN

SELE * from Prelod where condition1 and condition2 and condition3

Although it gave me the intended output, just want to make sure nothing logic error would happen…

Thank you…

 

[Chris Miller]

There's no limit about the number of conditions and ANDS or ORs, there's just a general limit of WHERE clause complexity, see SYS(3055), you can increase it. If you want to combine tens of conditions there should be a better way to express what you need, though.

Chriss

 

[Mandy_crw]

Ok Chriss... Thank you...

 

[mjcmkrsr]

Hi Mandy,

As Chris wrote there is no limit with the number of conditions. However, please be aware that the command line must NOT exceed 254 characters. Otherwise you would have to circumvent this limitation.

hth

MarK

 

[GriffMG]

One approach to this is to combine strings and just test once for many ands...

if m.field1+m.field2+m.field3 = "allgoodthings"...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Maximum # of characters per command line is 8,192

m.fieldX, Griff? m. is for memory variables, when you want to check conditions about the data in fields of a table, you don't use m. uness you explicitly want to compare fileds to variables of the same name in conditions like fieldX= m.fieldX.

Other than that detail, Griffs construct can help to shrink a set of conditions like field1='abc' and field2='mno' and field3='xyz' to field1+field2+field3='abcmnoxyz', sure. You just would need to pay close attention to the length of fields, if they are char and not varchar, or you need alltrim(field1)+','+alltrim(field2)+','+alltrim(field3)='abc,mno,xyz' to also prevent a match where field1 is 'a', field2 is 'bcmn' and field3 is 'oxyz' and all the single conditions are not fulfilled but they concatenate to abcmnoxyz, adding in commas prevents there to be such a mismatch. And it won't work if you have a mix of datatypes

I guess you want to compare records by comparing many fields of them, that's totally possible with the single tableA.field1=tableB.field1 AND tableA.field2=tableB.field2, etc. You have enough room for a lot of fields, you could instead work with checksums, though and you may scatter records to objects and compare all properties of two objects at the same time with COMPOBJ(obj1,obj2). That's betterdoabel in a scan loop than in a SQL query, though, as you need to run the SCATTER command, within SQL you can call functtions but not commands, and even using a trick like _vfp.docmd('command') turning a command execution into a function call of _vfp.docmd is very tricky to use.

Chriss

 

[GriffMG]

very good point m.field...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[GriffMG]

Am I right in thinking that VFP processes multiple ANDs from left to right and stops on the first one that returns a .f.?

So in this, fictious, case:

if .f. and messagebox("Help",52,"Never Happens")

The messagebox() will not be processed...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

True, and good tip, Griff,

Mandy, it means because there will be no difference of the result whether the where clause is [tt]condition1 AND condition2[/tt] or instead it is [tt]condition2 AND condition1[/tt], you should start with the condition that most likely is false to not check the other or further conditions. You don't get such shortcuts with OR, as neither a .F. or .T. result in a single condition would make the logical result of other conditions unimportant.

Also, in case you have indexes on some but not all fields, thats's not always a reason to add additional indexes, it can already make a query faster by first checking the indexed fields and it can even be faster to keep single checks instead of concatenating fields as you likely don't have an index on field1+field2+...


Chriss

 

[Mandy_crw]

Thank you Griff and Chriss....

 

[GriffMG]

Mandy

This reminds me of a riddle.

Can you make a sentence with the word AND in it five times in a row that still makes sense?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Chris Miller]

Nice one, Griff. I found it but won't spoil it.

Within VFP you can use any odd number of ands to make a valid expression. The only requirement is that a variable AND exists, which has a boolean value of .t. or .f.:

and=.t.
? and
? and and and
? and and and and and
? and and and and and and and
? and and and and and and and and and
? and and and and and and and and and and and

...and so on, until you reach the limit of command length or where/for clause complexity.

If you use an even number of ands you get the error "Missing operand."

Well, I already said in another thread asking about usage of .NULL. vs NULL from AlastairP:

rule of thumb therefore is to not use keywords as names of anything. Simply to not confuse yourself when reading the code.

That's also true for more and more complex conditions. If you need many ands, think about solving the problem in other ways.

In regard of strange grammar, you can talk of your fathers fathers fathers fathers fathers... even though there also is the shortening with grandfathers and great-grandfathers, that only gives you many more variations of expressing the same thing. In the end it's only limited by the finite age of the universe, Earth and most precisely perhaps, whenever evolution actually introduced two sexes.

Chriss

 

[Chris Miller]

Just out of curiosity, I wrote code that generates longer and longer expressions:

Public gnI, x, gcExpression
x  = .f.
gcExpression = 'x'
On Error Cancel
For gnI = 1 to 10000
  gcExpression = gcExpression + ' and x'
  evaluate(gcExpression)
EndFor

Using a short variable name x just to make the expression as short as possible and allowing as many ORs and ANDs as possible.

The code errors and thus cancels and stops when gnI reaches 1366, the expression then has a length of 8197, so clearly the maximum command length limit of 8192 is reached before the complexity limit.
Change that to use OR instead of AND, which generates shorter expressions, it works up to 1639 OR operators and the expression length then is exactly 8196.

When the code stops the status bar message becmes "do cancelled" and you can ? gnI, ? LEN(gcExpression) as those variables still exist as they are define PUBLIC. That's the only reason to do that.

I guess complexity doesn't rise very much with simple sequences of ANDs and ORs, but you'll practically fail in writing out such long conditions without any typo or other logical error before you reach the technical limitation, the limits will also vbe lower as there are only 26 single letter names, ome more if you allow all other characters expect numbers, but you'll not practically have such field names in your conditions.

So there's what you can get from experimenting. realistically with longer field names with 5 to 7 letter words instead of single letters perhaps 500-600 ANDs and ORs can be used, no real practical limitataion, or in other words, the practical limitation occurs earlier than the technical, as nobody would ever write out such long conditions, they're not realistic.

Chriss

 

[Dan Olsson]

Can you make a sentence with the word AND in it five times in a row that still makes sense?

Yes I can

Spoiler

A store owner ordered a sign for his shop which should read "Johnson and sons".
When he got it he was not happy about it it, so he called the signmaker and said:
There should be more space between Johnson and and and and and sons.

 

[GriffMG]

@Dan

I know it as a pub sign, but yours works too.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​