Editing the SQL

This option gives you the ability to edit or enter the SQL statement. If you clicked "Modify SQL" from the Competition screen, the SQL generated to this point will be editable.

Note: Once you have edited the SQL statement, you will no longer be able to modify the SQL rule using the visual editors.

The SQL statement contains named fields that the indexing process looks for, these include:

ISYS_KEY_#	Indicates the column is the n'th identifying key.
ISYS_TITLE	Indicates the column should be used as the document title.
ISYS_MODIFIED	Indicates that the column is to be used when identifying if the record has been changed.

For every ISYS_KEY_#, there must be a corresponding parameter in the where clause.

There are two parts to the SQL statement that Perceptive Search uses at different times, during the indexing and during document views. When indexing, Perceptive Search first scans for new or changed documents, queuing them for update. Once the scan is complete, it re-opens each document to read the text to apply it to the index.

The SQL statement that you enter must be able to handle this dual purpose of scanning all records, and returning a single record based on a series of keys. To handle this situation, you can encode a portion of the SQL state in {{ and }}. Perceptive Search will omit any SQL contained within these brackets during scan phase, and then include it when trying to open individual documents.

Consider the following example:

SELECT 
  Books.BookId AS ISYS_KEY_0,
  Books.Title AS ISYS_TITLE, 
  Books.BookId, 
  Books.Title, 
  Books.ISBN, 
  Books.Description
FROM Books
{{
  WHERE 
    (Books.BookId = ?)
}}

This sample includes one key; the BooksId column. In the "Read" section, we have defined a parameter to receive this key value when an individual record is requested.