The CREATE VIEW statement defines and stores (for the duration of the session with Index Server) a set of properties that you frequently use. You can create a temporary view or use one of several predefined views supplied with the SQL extensions. Creating a VIEW allows you to use SELECT * queries, which normally cannot be carried out in Index Server. Without a VIEW, you would have to specify the full list of columns each time you want to run such a query.
NOTE It is possible to issue a SELECT against a view where the Select_List is a subset of the columns in the view definition
The simple query specification consists of a SELECT list and a FROM clause.
CREATE VIEW #View_Name AS SELECT Select_List FROM_Clause
| View_Name | Specifies the name for the View. The viewname must be preceded by a pound (#) sign. |
| Select_List | Specifies the list of column aliases (properties) making up the table (rowset) that is returned as a result of the query. |
| FROM_Clause | Specifies the files on which to perform the search. |
Once you have a name associated with a view definition using the CREATE VIEW statement, you cannot associated it with another view definition. For example, if you execute the following two CREATE VIEW statements in succession:
CREATE VIEW #MyView1 AS SELECT DocAuthor FROM SCOPE() CREATE VIEW #MyView1 AS SELECT FileName FROM SCOPE()
The second CREATE VIEW is an attempt at redefinition and will fail.
The view name, along with its view definition is implicitly associated with the catalog active at the time you define the view. If you switch catalogs, that view will not be found and you must redefine it. So in the following pseudo code flow:
<default catalog is cat1>
CREATE VIEW #MyView1 AS SELECT DocAuthor, size FROM SCOPE()
<set current catalog to cat2>
SELECT * FROM #MyView1 will result in an error since #MyView1 has not been defined to cat2.
<set current catalog back to cat1>
SELECT * FROM #MyView1 will return hits if there are files within SCOPE().
Suppose you often query for the following:
CREATE VIEW #MySpecView
AS SELECT DocAuthor, FileName, size, access
FROM SCOPE(' DEEP TRAVERSAL OF "/specs/database/integration" ',
SHALLOW TRAVERSAL OF "/current/specs" ')
As a result, all future queries can be expressed in a simpler manner. For example:
SELECT * FROM #MySpecView WHERE size > 100000
SELECT * FROM #MySpecView
WHERE CONTAINS (' "index search" ') > 0
Select FileName, size FROM #MySpecView
WHERE FREETEXT ('how do I search with Index Server') > 0
AND FileName LIKE 'm%.doc'
The SQL extensions include several predefined views that you can use without writing an explicit CREATE VIEW statement. Note that because the predefined view names are not temporary, they do not need to be preceded by the pound (#) sign.
| View | Properties | Description |
|---|---|---|
| FILEINFO | path, FileName, size, write, attrib | Standard file result list |
| FILEINFO_ABSTRACT | path, FileName, size, write, attrib, Characterization | Standard file result list plus abstract |
| EXTENDED_FILEINFO | path, FileName, size, write, attrib, DocAuthor, DocTitle, DocSubject, DocKeywords | Extended file result list |
| WEBINFO | Vpath, path, DocTitle, FileName, size, Characterization, write | Standard Web results |
| EXTENDED_WEBINFO | Vpath, path, DocTitle, FileName, size, Characterization, write, DocAuthor, DocSubject, DocKeywords | Extended Web results |
These predefined views are always available, independent of which catalog you are currently using.
The following example shows the conceptual SQL statement to the supplied FILEINFO view. Note that the predefined views are constructed using the default scope.
CREATE VIEW FILEINFO AS SELECT path, FileName, size, write, attrib FROM SCOPE()
After creating the view of the previous example, you can issue queries such as:
SELECT * FROM FILEINFO WHERE size > 20000 SELECT path, FileName, attrib FROM FILEINFO WHERE attrib = ALL ARRAY [0x820] OR FileName LIKE 'arch%.xls'