Setting Up SQL Searches

You can do a search for records using Structured Query Language (SQL). SQL is a computer language used to create, modify, retrieve and manipulate data from relational database management systems, such as Polaris. In the Polaris Find Tool, SQL is used for data retrieval only. You cannot manipulate data in the Polaris database through SQL searching.

To use all the options for SQL searching in the Find Tool, you must have permissions set in Polaris Administration. Separate permissions are required for accessing the SQL searching mode and saving search strings to the database. See Special Permissions for SQL Searches in the Find Tool.

Tip:
You can save text, even if it is not a legitimate SQL search, as a rough SQL template that users can use as a basis for searching. They can edit the search in the Search Criteria box without saving the changes to the template.

In the Polaris Find Tool, the SQL query must use the following structure: SELECT Column From Table Where Limit criteria. The column name must match the column name in the database.

Examples:

To find item records without an assigned collection, type: SELECT ItemRecordID FROM CircItemRecords WHERE AssignedCollectionID IS NULL

To find item records with no statistical code, type:SELECT ItemRecordID FROM CircItemRecords WHERE StatisticalCodeID IS NULL

To find item records with the circulation status of Lost, type:SELECT ItemRecordID FROM CircItemRecords WHERE ItemStatusID = 7

To find item records that have a temporary shelf location, type:SELECT ItemRecordID FROM ItemRecordDetails WHERE TemporaryShelfLocation IS not NULL

To find patron records where the registration expiration date is before or equal to October 31, 2009, type:SELECT PatronID from PatronRegistration WHERE ExpirationDate <= '10/31/09'

The SELECT command retrieves zero or more rows from one or more tables in a database. Commonly available keywords related to SELECT include:

FROM - Indicates the tables from which the data is to be taken, as well as how the tables join to each other.
WHERE - Identifies which rows to retrieve, or applied to GROUP BY.
GROUP BY - Combines rows with related values into elements of a smaller set of rows.
HAVING - Identifies which of the “combined rows” are to be retrieved. HAVING acts much like WHERE, but it operates on the results of the GROUP BY function.
ORDER BY - Identifies which columns are used to sort the resulting data.

When the search results are displayed, you can print the results for a quick report. See Print search results from the Find Tool.