The %INSET predicate allows you to filter a result set by selecting those data values that match the values specified in valueset. This match is successful when a scalar-expression value matches a value in valueset. If the valueset values do not match any of the scalar expression values, %INSET returns the null string. This match is always performed on the logical (internal storage) data value, regardless of the display mode.
%INSET is never true for a NULL value. Therefore, it will not match a NULL in the scalar-expression with a NULL in valueset.
%INSET, like the other comparison conditions, is used in the WHERE clause or the HAVING clause of a SELECT statement.
%INSET enables filtering of field values using an abstract, programmatically specified set of matching values. Specifically, it enables filtering of RowId field values using an abstract, programmatically specified temp-file or bitmap index, where valueset behaves similar to the lowest subscript layer of a bitmap index or a regular index.
The user-defined class is derived from the abstract class %SQL.AbstractFindOpens in a new tab. this abstract class defines the ContainsItem()Opens in a new tab method, which is the only method supported by %INSET. The ContainsItem() method returns the valueset. Refer to %SQL.AbstractFindOpens in a new tab for further details.
The optional %INSET SIZE clause provides the integer nn, which specifies an order-of-magnitude estimate of the number of values in valueset. InterSystems IRIS uses this order-of-magnitude estimate to determine the optimal query plan. Specify nn as one of the following literals: 10, 100, 1000, 10000, etc. Because nn must be available as a constant value at compile time, it must be specified as a literal in all SQL code. Note that nesting parentheses must be specified as shown for all SQL, with the exception of Embedded SQL.
%INSET and %FIND Compared
%INSET is the simplest and most general interface. It supports the ContainsItem() method.
%FIND supports iteration over bitmap chunks using a bitmap index. It emulates the functionality of the ObjectScript $ORDER function, supporting NextChunk(), PreviousChunk(), and GetChunk() iteration methods, as well as the ContainsItem() method.