Say I have a database with numeric IDs, and I’d like to make a selection of all records where the IDs exist in a certain array. I could certainly make a loop with a bunch of SELECTADDITIONALs, but there must be a less gormy way to go about it, right? If I could marry MySQL’s syntax to Panorama’s, such a query might look something like:
SELECT id IN (3415, 4328, 5181, 9912)
In case it’s relevant to the solutions folks come up with, in this particular case, the IDs that I’m searching on are not unique in the database. Each ID can occur multiple times, and I want to select all of them.
You could nest your selection criteria with “ANY of the following is true” in the Find/Select dialog (boolean OR). The resulting formula (for use with a select statement in a procedure):
«id» contains |||3415||| or «id» contains |||4328||| or «id» contains |||5181||| or «id» contains |||9912|||
So this is perfect for what I’m doing… I’m kind of in awe, because even after reading the documentation of arraycontains, I still would not have realized that I could use this in a SELECT statement. So thank you!
«id» contains |||3415||| or «id» contains |||4328||| or «id» contains |||5181||| or «id» contains |||9912|||
Something like this would have been my backup approach, but with a long list of IDs, I’m dissatisfied with it… maybe it’s an aesthetic objection more than anything else. Still, good to know that there are options.
I generally do not respond to queries on this forum, mainly because I could not hope to do better than the true experts here. But your enquiry intrigued me, because I had occasion to do something very similar, and came up with something different from the advice you received
I interpret our question as this: you already have a text array (consisting of numbers), which you obtained from some earlier step, perhaps from an ARRAYBUILD statement. You don’t know in advance what the array will contain. You want to select any record which is included in that array. Is that the correct interpretation?
I used the following to do this. Does this work for you? The array containing your numbers is “myArray”. The field containing numbers is «ID».
ARRAYBUILD myArray,¶,"", (formula to produce the array)
SELECT search(myArray,str(«ID») > 0
The benefit of this approach is that it doesn’t matter how myArray is formed, because it is obtained on the fly.
Thanks for pitching in! The more participants the better
Reluctantly, however, I must point out a problem with your solution. In fact, I would recommend that you revise your code if you are still using it.
Using the search( function will work fine if all of the ID values are the same length. But what if some are 2 digits, some are 3 digits, some 4, 5 or 6? In that case, you’ll get false matches. For example, suppose the current ID is 36. If your array contains 36, it will match. But it will also match if the array contains 369, 836, 2364, etc. I assume these would all be false matches that you don’t actually want. The solution is to use the arraycontains( function as suggested earlier. Unlike search(, the arraycontains( function function will only match on the exact value, there will be no match if the value is a subsection of an array value. Here is a rewritten version of your code.
ARRAYBUILD myArray,¶,"", (formula to produce the array)
SELECT arraycontains(myArray,str(«ID»),¶)
You correctly interpreted my question–the array is a variable that is created earlier in the procedure, and is different each time the procedure is run. I probably should have included that in my original post, but I lucked out anyway in that Dave’s solution works equally as well with a named array as it does with a comma-delimited string. So when I adapted it for my code, it looks more like:
Select arraycontains(myArray,str( id),",")
Thank you for sharing another approach! In my case, the IDs are in fact all the same length, so even with Jim’s caution, your solution would work for me.
I’m going to put on my “professional programmer” hat here. Even if this solution happens to work for you now, I would advise against using it. Perhaps sometime in the future, in a year or two, you’ll start adding ID’s with additional digits. By then, you’ll have forgotten all about the fact that this code doesn’t work correctly in that situation. It might produce incorrect results for weeks, months or even years without you ever noticing it, then when you do notice it, you’ll have to track it down and figure out the problem from scratch, since you’ll undoubtably have forgotten all about today’s discussion.
This is why I also recommend that Vic revise his code now, while this is all fresh. It may or may not be working ok for him now, but it could be a difficult to track down problem in the future.
Vic, I want to commend you again for piping up. Please don’t be discouraged by the fact that there was a bit of a problem with your solution. Hopefully this discussion has been educational for others, and perhaps the fact that you spoke up will save you from some problems down the road. I think this task (selecting based on contents of an array) is probably pretty common, so it’s good to have a full discussion of the pros and cons of various techniques.
By the way, there is a way that you can use the search function to do this reliably, by adding some extra separator characters to the formula like this:
select search(¶+myArray+¶,¶+str(«ID»)+¶)>0
This will work 100% reliably just like the arraycontains( solution, even if the array elements have different lengths.
All of us old guard will endorse this sentiment. Many of my procedures take longer than they need because, wherever practicable, I have endeavoured to write a general solution which goes beyond my immediate needs. At the very least SAO, put in a comment that highlights the limitations of your current solution.
Arraycontains( returns true (-1) when the second parameter is contained in the array, and false (0) when it is not. The result would never be greater than 0. I expect you were editing Vic’s statement, and just forgot to delete those last two characters.
Yes, that is exactly what happened, and I also left out a closing paren, as Scott pointed out. I have used my godlike powers on this forum to go back and correct my original post If only mistakes in real life could be fixed so easily!
