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*' ) |
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.
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |