INLIST parameters 1

[StewartUK]

Did I imagine it, or was the limit of 24 parameters in an INLIST statement removed in VFP9?

Thanks for any enlightment,

Stewart

 

[Mike Lewis]

Stewart,

Gosh, you must have a good imagination.

Seriously ...

No, the 24-item limit remains in place. You might be thinking of the way the IN operator in a SQL WHERE statement has changed. In earlier versions, IN mapped to INLIST(), which effectively limited it to 24 items. That mapping was removed in 9.0, which means you can list more items with IN, but it is no longer Rushmore-optimised.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[craigsboyd]

You could always roll-your-own using the $, or Occurs(), or ATC(), etc. Simply providing a comma delimited (or otherwise delimited) string of the words or phrases you are attempting to rule in or out. Obviously with data such as numeric, date, datetime, etc. you would need to use TRANSFORM() in order to get a string comparison. Another way around this is to use multiple INLIST() calls, such as INLIST(myvar, 0, 1, 2,...23) OR INLIST(myvar, 24, 25, 26...). In any event it is a silly limit, but not one that is impossible to work around.

SweetPotato Software Website
My Blog

 

[StewartUK]

Craig,

Thanks for your tip about using AT or $ with a comma separated list, thanks! Used to use that a lot but forgot about it.

Until now I'd been building a variable, counting up to 24 and then inserting "OR INLIST(..." and so on.

Stewart

 

[Olaf Doschke]

I'd say the limit is simply inherited from the limit of parameters to user defeined functions.

With large number of items, I'd store values in a cursor and do an inner join with that.

Bye, Olaf.

 

[Mike Lewis]

Just to follow up my point about the IN operator not being Rushmore-optimised ....

It seems that this works slightly differently in 9.0, in that, as soon as it finds the first matching term, it stops searching. Earlier versions would carry on to the end of the list. So you can speed up queries slightly by putting the most common terms early in the IN list.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jimstarr]

I didn't know you could use a comma-delimited string with $! What's the syntax? Is it documented?

Jim

 

[Stella740pl]

From what I understand, you can delimit it with anything that couldn't occur in the list items: #, or %, or ^, for that matter.

Just try it like this:

MyItem="ABC"

MyList1=[ABC#DEF#XYZ]
MyList2=[abc^def^xyz]
MyList3=[ABC,Def,xyZ]

?MyItem$MyList1
?MyItem$MyList2
?MyItem$MyList3

 

[StewartUK]

Yes, that's how I used to use it. I heard someone once say that AT worked faster than $.

Stewart

 

[Stella740pl]

Well, it should be so; unlike AT(), $ operator is not Rushmore optimizable.

 

[Stella740pl]

Also, you might want to use ATC() instead, to perform case-insensitive search.

 

[alvechurchdata]

I heard someone once say that AT worked faster than $.

There's an interesting comparison of AT, $ and ASCAN on Wiki:

http://fox.wikis.com/wc.dll?Wiki~ATvs$vsASCAN~VFP

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Stella740pl]

Thanks, interesting article (even though the best way to read it would be to print it out landscape-oriented on legal-size paper using color printer). A star from me.

At least the first 3 or 4 charts were more or less predictable. I always thought that TANSTAAFL were not much of an accepted scientific principle and couldn't be taken as an axiom. ;-)

(I rubbed my eyes in disbelief and re-read several times that part: The quick answer follows from the TANSTAAFL principle (see AcroNyms). Since AT and ASCAN give you more information, namely the position of the matching candidate, they will take longer. Thus, $ wins. Q.E.D. I felt relieved when I realized that this catchy introduction shouldn't be taken seriously.)

 

[TamarGranor]

Another way to handle this sort of problem generally is to parse the list of items and then do the search. You could use ALINES() to create an array and then use ASCAN() to search.

But VFP being a database application, the best is probably to put the items into a cursor and search that.

Tamar