scalar-expression %INSET valueset [SIZE ((nn))]
|scalar-expression||A scalar expression (most commonly the RowId field of a table) whose values are being compared with valueset.|
|valueset||An object reference (oref) to a user-defined object that implements a ContainsItem() method. This method takes a set of data values and returns a boolean when there is a match with a value in scalar-expression.|
|SIZE ((nn))||Optional — An order-of-magnitude integer (10, 100, 1000, etc.) used for query optimization.|
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.
%INSET uses the same collation type as the column it is matched against. By default, string data type fields are defined with SQLUPPER collation, which is not case-sensitive. The “Collation” chapter of Using InterSystems SQL provides details on defining the string collation default for the current namespace and specifying a non-default field collation type when defining a field/property. If you assign a different collation type to the column, you must also apply this collation type to the %INSET substring. Refer to %SQLUPPER for further information on case transformation functions.
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.