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
DS Basic
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 385
- Joined: Tue Oct 07, 2003 4:55 am
Re: DS Basic
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
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
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
Regards,
Michael Hester
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Re: DS Basic
You would be better to use UPCASE(ARG1) = 'FIAT'Billy wrote: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
No. But you could write your own function INLIST with two arguments:Billy wrote: Does D/S have a faster method like the INLIST function in SQL?
SELECT * FROM table WHERE column INLIST ('A', 'B', 'C')
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
Code: Select all
Found = INLIST("X", "A,B,C,D,E,F,G,X,Y,Z")
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Re: DS Basic
Maybe I'm really dense, but I believe I directly replied on point to the original poster's request.Billy wrote: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?
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.