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[,...]])

Description

SEARCH_INDEX invokes the index-name Find() 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.

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 SQL Search text search tool.

Arguments

table-name

An optional argument specifying 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

An optional parameter or a comma-separated list of parameters to be passed to the index’s Find() method.

Examples

The following example shows the usage of the %FIND predicate with SEARCH_INDEX:

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