%INSET

Matches a value to a set of generated values.

Synopsis

scalar-expression %INSET valueset [SIZE ((nn))]

Arguments

Argument	Description
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.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.

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

%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.