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:
Refer to %SQL.AbstractFindOpens in a new tab for further details concerning these four methods.
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.