Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.Opens in a new tab

For information on migrating to InterSystems IRISOpens in a new tab, see Why Migrate to InterSystems IRIS?

SEARCH_INDEX (SQL)

A function that returns a set of values from the index’s Find() method.

Synopsis

SEARCH_INDEX([[schema-name.]table-name.]index-name[,findparam[,...])

Arguments

Argument Description
table-name Optional — The name of an existing table for which index-name is defined. Cannot be a view. The table’s schema_name is optional. If omitted, all tables specified in the FROM clause are searched.
index-name The index to be searched. The SqlName of the index map of an existing index.
findparam Optional — An parameter or a comma-separated list of parameters to be passed to the index’s Find() method.

Description

SEARCH_INDEX invokes the index-nameFind() method and returns a set of values. You can optionally pass parameters to this Find() method. For example, SEARCH_INDEX(Sample.Person.NameIDX) invokes the Sample.Person.NameIDXFind() method.

SEARCH_INDEX can be used with the %FIND predicate in a WHERE clause to supply the oref of an object that provides an abstract representation encapsulating a set of values. These values are commonly row IDs returned by a method called at query run time. SEARCH_INDEX invokes the index’s Find() method to return this oref. This usage is shown in the following example:

SELECT Name FROM Sample.Person AS P
WHERE P.Name %FIND SEARCH_INDEX(Sample.Person.NameIDX)

The index must be found within the tables referenced by the SQL statement. An SQLCODE -151 error is generated if the specified index-name does not exist within the tables used by the SQL statement. An SQLCODE -152 error is generated if the specified index-name is not fully qualified, and is therefore ambiguous (could refer to more than one existing index) within the tables used by the SQL statement.

If the index exists, but it has no corresponding Find() method, a runtime SQLCODE -149 error is generated “SQL Function encountered an error”, the error being <METHOD DOES NOT EXIST>.

For further details on the use of SEARCH_INDEX, refer to the iFind Search Tool chapter of Using iKnow.

See Also

Next sectionSECOND
Previous sectionRTRIM
FeedbackOpens in a new tab