access %List property from sql
Hi,
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,
Sebastian
Comments
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,
Seabstian
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)
This article and the related example in OEX may be helful.
Effective use of Collection Indexing and Querying Collections through SQL