DS Basic

[Billy]

I need to check a range of values for a column and am creating a routine to check for the list of possible values

Begin Case
Case Arg1 = 'Fiat' or Arg1 = 'FIAT' or Arg1 = 'fiat' THEN
....
Case Arg1 = 'Ford' or Arg1 = 'FORD' or Arg1 = 'ford' THEN
....
....
End

Does D/S have a faster method like the INLIST function in SQL?
SELECT * FROM table WHERE column INLIST ('A', 'B', 'C')


thanks
Billy

[Amos.Rosmarin]

You can do :

Case upCase(Arg1) = 'FIAT'

if all cars has the same treatment you can use the index function

Index ( "FIAT,FORD,VOLVO" , UpCase ( Arg1) , 1 ) > 0

If arg1 is in th list the index value will be > 0 ( but be carefull Arg1 = "F" will also return value > 0)

HTH,
Amos

[kcbland]

The best solution is to put the list of values into an array and use the LOCATE statement.

Sorry to disagree Amos, but you even pointed out your solution hits on substring matches.

If you search the forum, this was just covered a few weeks back. I covered the various methods and the differences and problems with using COUNT, INDEX, and LOCATE.

viewtopic.php?t=85124&highlight=locate+index+count

[mhester]

I believe that the original post was specific to branching based on a set of certain values. In this case what Amos described in his post or the original post would be sufficient. The locate statement would not be appropriate here, although Ken is correct that when searching arrays for values then locate is the most efficient method.

Regards,

Michael Hester

[kcbland]

I need to check a range of values for a column and am creating a routine to check for the list of possible values

Begin Case
Case Arg1 = 'Fiat' or Arg1 = 'FIAT' or Arg1 = 'fiat' THEN
....
Case Arg1 = 'Ford' or Arg1 = 'FORD' or Arg1 = 'ford' THEN
....
....
End

You would be better to use UPCASE(ARG1) = 'FIAT'

Does D/S have a faster method like the INLIST function in SQL?
SELECT * FROM table WHERE column INLIST ('A', 'B', 'C')

No. But you could write your own function INLIST with two arguments:

Arg1 = Find Value
Arg2 = Comma Separated list of elements

The logic would be something like:

Code: Select all

FindValue = Arg1
ListOfValues = Arg2
CONVERT "," TO @AM IN ListOfValues
LOCATE FindValue IN ListOfValues SETTING POSITION Then Found = @TRUE Else Found = @FALSE
Ans = Found

Usage would be something like:

Code: Select all

Found = INLIST("X", "A,B,C,D,E,F,G,X,Y,Z")

If you needed case insensitivity, create a version of the routine that upcases.

This routine is not the most efficient, because each call will reconvert the comma separated list into an array every time. But, it is a very funtional and scalable routine should you have a lengthy list of values, as compared to the CASE construct which becomes unwieldy as the number of elements increases.

[mhester]

Ken et al,

I don't think that anyone would disagree that the locate will be more efficient (with large arrays) than using index or case statements to define boolean conditions.

What you describe is fine if all you want is a boolean comparison to return found or not found. If there are other things you want to do based on a branch then the case would be more appropriate.

In the post we do not know what the author wants to do once a condition is met?

My general rule for routines is that if they take up more than one page of code I try to find a different way to accomplish the same thing. (unless of course 90% of the lines are comments)

Regards,

Michael Hester

[kcbland]

I need to check a range of values for a column and am creating a routine to check for the list of possible values
...
Does D/S have a faster method like the INLIST function in SQL?

Maybe I'm really dense, but I believe I directly replied on point to the original poster's request.

Billy is creating a routine, and wanted to know if there was something like INLIST. I answered no, but gave him the code to write his own equivalent. In addition, I cautioned him away from using INDEX, as a substring match would return a false position and therefore LOCATE with values in discrete cells would prevent that from happening.

I hope I understood his original problem correctly.

[ray.wurlod]

LOCATE is fine up to a point, and better if the searchable array is kept sorted. But for very large lists (say more than about 100KB's worth), a hashed file of the (upper-cased) words, and Upcase(SearchWord) as the reference key expression, will be even more efficient.