How Create Lists Works

You can use the Create Lists function to create a list of record numbers, called a review file, by applying a query against a set of records known as a range. All records listed within a review file must have the same record type.

You create a query in one of Create Lists's query builders. A query consists of one or more query terms. Each term specifies:

• A target record type and field identifier that define where to look for the data that determines whether the term matches a given record. See Identifying Data below.
• An operator that defines how the data from the record is compared to the term's operand(s). In the default Classic query builder, operators are labeled "conditions" and include relationships such as "equal to", "greater than", "less than", and so forth. The full set of operators recognized by Create Lists is described in the topic Using Relational Operators.
• Zero or more operands. For example, a term using the "equal to" operator requires just one operand. The "between" operator requires two operands. The "exists" and "not exists" operators do not require any operands. The "in" operator can have an arbitrary number of operands.

You connect query terms with Boolean operators (AND and OR). You can group terms using parentheses. See Logical Operator Precedence and Grouping below.

Identifying Data

To determine whether a query term matches a given record, Create Lists uses the term's record type and field delimiter to find the data that it must compare to the term's operand(s).

Valid field identifiers include:

• A fixed-length field number
• A variable-length field tag
• A MARC tag, indicator, and subfield pattern
• A location in a "special field", such as a leader or control field
• Additional properties, such as those described in the topics on Using Property Fields in Searches

Create Lists uses a query term's record type to distinguish between potentially ambiguous field identifiers. For example, all records have a "created date", so a term that matches against a specified "created date" must identify the type of record to search; "bibs whose created date is greater than 2012" (record type = bib) or "bibs with a linked item whose created date is greater than 2012" (record type = item).

Properties and Relationships

The data extracted from a record for the purposes of term-matching can be found as either a property of the record or through a relationship that record has to another entity. A record has one (and only one) value for a property, but it might have many values for data that the system accesses through entity relationships if the record is related to multiple entities.

The Sierra relational database includes a variety of entities stored in their own tables. These entities help produce a normalized database and avoid potential data integrity violations. You can also use these entities to construct more powerful queries, as described in Applying Terms to Related Entities below.

Logical Operator Precedence and Grouping

Create Lists provides two Boolean operators for combining query terms, AND and OR. The precedence assigned to these operators is the same as that found in most programming and query languages (that is, AND has a higher precedence than OR).

To help illustrate logical operator precedence, assume you want to find all Spanish language print books or ebooks. You want to construct the query equivalent of "bibs where the language is Spanish and the material type is print book or ebook". You try the Create Lists query:

LANG = 'spa' AND MAT_TYPE = 'p' OR MAT_TYPE = 'e'

This query does not return the results you want. The query finds Spanish print books (LANG = 'spa' AND MAT_TYPE = 'p') and all ebooks in any language (MAT_TYPE = 'e'). Due to operator precedence, the query interpreter "groups" the first two terms together using AND, and then applies the OR to add together the grouped terms and the third term. To get the results you want, you must provide explicit grouping instructions using parentheses to override the inherent precedence:

LANG = 'spa' AND (MAT_TYPE = 'p' OR MAT_TYPE = 'e')

Combining Query Terms

By default, the Create Lists query interpreter translates a set of query terms into individual SELECT statements, one per term, and connects them using INTERSECT or UNION for AND and OR, respectively. To improve efficiency, the interpreter combines multiple query terms together into a single SELECT statement if all of the following are true:

• There are at least three terms in the query (or group).
• All of the terms specify the same record type and field identifier.
• Only "simple" SQL is required for that field identifier (for example, if no extra table joins or type discriminator values are required).

Create Lists combines the following query into a single SELECT statement with five conditions, rather than five SELECT statements UNIONed together.

LOCATION = 'a' OR LOCATION = 'b' OR LOCATION = 'c' OR LOCATION = 'd' OR LOCATION = 'e'

Applying Terms to Related Entities

When you apply terms to a related entity, such as a variable-length field, the Classic and Enhanced query builders each handle your statement differently.

In the Classic Query Builder (default)

When a term built in the Classic editor applies to a related entity, the interpreter almost always generates a separate SQL SELECT statement for that term, and INTERSECTs or UNIONs that result with the main query's result set.

Assume you want to find ebooks about children's music. You use a query that finds records having both a subject that matches "electronic books" as well as a subject that matches "children's music". You want the subject matching to be fairly general, so you use two HAS terms for each of the target subjects. (Bear in mind that this example is for illustrative purposes only.) Your Create Lists query resembles the following:

SUBJECT HAS "electronic" AND SUBJECT HAS "books" AND SUBJECT HAS "children's" AND SUBJECT HAS "music"

Assume the following two bib records appear in the system:

b100123
t 245 The Mozart effect for children. |h [ebook]
d 655 Children's music.
d 655 Electronic books.

b100456
t 245 Skrillex goes to preschool. |h [large print]
d 655 Electronic dance music.
d 655 Children's books.

You probably want your query to find the first record, but not the second. However, Create Lists interprets your query as "bibliographic records that have any subject containing 'electronic' and any subject containing 'books' and any subject containing 'children's' and any subject containing 'music'". This matches both of the records.

You might try grouping to refine the query:

    (SUBJECT HAS "electronic" AND SUBJECT HAS "books") AND (SUBJECT HAS "children's" AND SUBJECT HAS "music")

That still does not provide the results you want. The query interpreter always treats terms that apply to relationships (in this case, a bibliographic record's relationship to its subject fields) separately, and does not combine them (as described in Combining Query Terms above), even when those terms are explicitly grouped as in the refined query.

There are two exceptions. The Classic interpreter combines terms if:

• There are more than two terms.
• The term applies to a specially-configured record type/variable-length field pair. By default, this configuration applies to call numbers (c-tagged fields for bibliographic, item, and holdings records).

In the Enhanced Query Builder

In the Create Lists Enhanced editor, you can apply multiple terms per target record type and field pair. You can effectively define a query to search for bibliographic records in which:

    (SUBJECT HAS "electronic" AND HAS "books") AND (SUBJECT HAS "children's" AND HAS "music")

This query returns only bibliographic records that include a subject field containing both "electronic" and "books" AND a subject field containing both "children's" and "music". In the example above, bibliographic record b100456 does not satisfy those conditions, while b100123 does.