Caché SQL Reference
%INSET
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

Matches a value to a set of generated values.
Synopsis
scalar-expression %INSET valueset [SIZE ((nn))]
Arguments
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.
Description
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, 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.AbstractFind. this abstract class defines the ContainsItem() method, which is the only method supported by %INSET. The ContainsItem() method returns the valueset. Refer to %SQL.AbstractFind for further details.
Collation Types
%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 Caché 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.
SIZE Clause
The optional %INSET SIZE clause provides the integer nn, which specifies an order-of-magnitude estimate of the number of values in valueset. Caché 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
See Also