scalar-expression %FIND 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 bitmap chunks iteration methods and the 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 %FIND predicate allows you to filter a result set by selecting those data values that match the values specified in valueset, iterating through values in a sequence of bitmap chunks. 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, %FIND returns the null string. This match is always performed on the logical (internal storage) data value, regardless of the display mode.
%FIND, like the other comparison conditions, is used in the WHERE clause or the HAVING clause of a SELECT statement.
%FIND 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 bitmap, where valueset behaves similar to the subscript layer of a bitmap 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 boolean method. The ContainsItem() method matches the scalar-expression values to the valueset values.
Iteration through values in a sequence of bitmap chunks is performed using the following three methods:
GetChunk(c)Opens in a new tab, which returns the bitmap chunk with chunk number c.
NextChunk(.c)Opens in a new tab, which returns the first bitmap chunk with chunk number > c.
PreviousChunk(.c)Opens in a new tab, which returns the first bitmap chunk with chunk number < c.
Refer to %SQL.AbstractFindOpens in a new tab for further details concerning these four methods.
%FIND 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 %FIND substring. Refer to %SQLUPPER for further information on case transformation functions.
The optional %FIND 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.
%FIND and %INSET 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.