View search

To use a full text search on a view or derived table, you must build a text index on the columns in the base table that you want to perform a full text search on. The following statements create a view on the MarketingInformation table in the sample database, which already has a text index name, and then perform a full text search on that view.

To create a view on the MarketingInformation base table, execute the following statement:

CREATE VIEW MarketingInfoView AS
SELECT MI.ProductID AS ProdID,
     MI."Description" AS "Desc"
FROM GROUPO.MarketingInformation AS MI
WHERE MI."ID" > 3

Using the following statement, you can query the view using the text index on the underlying table.

SELECT *
FROM MarketingInfoView
WHERE CONTAINS ( "Desc", 'Cap OR Tee*' )

You can also execute the following statement to query a derived table using the text index on the underlying table.

SELECT *
FROM (
     SELECT MI.ProductID, MI."Description"
     FROM MarketingInformation AS MI
     WHERE MI."ID" > 4 ) AS dt ( P_ID, "Desc" )
WHERE CONTAINS ( "Desc", 'Base*' )

Note: The columns on which you want to run the full text search must be included in the SELECT list of the view or derived table.

Searching a view using a text index on the underlying base table is restricted as follows:

The view cannot contain a TOP, FIRST, DISTINCT, GROUP BY, ORDER BY, UNION, INTERSECT, EXCEPT clause, or window function.
The view cannot contain aggregate functions.
A CONTAINS query can refer to a base table inside a view, but not to a base table inside a view that is inside another view.