Sebastian Thiele · Mar 26, 2021

access %List property from sql


is it possible to access a property defined as %List from sql? I´d like to search for a object containing a specific value in it´s %List defined property. Is there a predefined sql function for that within IRIS?

best regards,

0 0 7 127
Log in or sign up to continue


I think you can accomplish this is $LISTFIND. It searches a list for a value, and returns its position in the list. If the item isn't found in the list, it will return a 0. For example, if you've got a list of colors called colorlist you'd use "where $LISTFIND(colorlist,'blue') > 0" in your SQL predicate to only include rows that have "blue" in their list. If the list contained "red", "blue", and "green" in that order, the $LISTFIND would return a 2. If the list contained "orange", "yellow", "taupe", the $LISTFIND would return a 0 because "blue" wasn't found and that row would be excluded.

Hi David,

thanks that worked for me.

best regards,

Glad to hear that, Sebastian! Would you mind marking my response as the correct answer, then?

Use %INLIST it matches a value to the elements in a %List structured list.

Unfortunately, %INLIST does not use indexes (at least it was in Caché, I do not know how it is now in IRIS), which I made a note about in my article.

$LISTFIND certainly does its job well, but there is a better solution.

As the data grows, the search speed will drop, since this solution does not use indexes in any way.
Therefore, the best solution is to use the predicate FOR SOME %ELEMENT.

For more details with examples, see one of my articles: SQL Performance Resources (item k. Indexing of non-atomic attributes)