Skip to main content
ExLibris
  • Subscribe by RSS
    • Back
    Voyager
    Ex Libris Knowledge Center

    Commonly used SQL operators in Voyager Prepackaged Access Reports

    1. Last updated
    2. Save as PDF
    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    Some commonly used SQL operators in Voyager Prepackaged Access Reports, with a few usage examples.

    Answer

    An operator is a sign or symbol that specifies the type of calculation to perform within an expression.  Access supports a variety of operators.  The following provides information about some of the most commonly used operators in Prepackaged Access Reports.  For a "Table of operators" see the Microsoft website: https://support.microsoft.com/en-us/office/table-of-operators-e1bc04d5-8b76-429f-a252-e9223117d6bd

    Having trouble seeing a long BLOB function or other lengthy expression?  Put your cursor in the cell you want to view and hit Shift-F2.  This opens the contents of the cell in an Access zoom box.

    Like

    •Wildcard character(s)

    clipboard_e2190a544563aea5b8d77342dfa1d95f0.png

    The above query does pattern matching on the Link field. An asterisk matches any sequence of zero or more characters.

    Is Null

    •Unknown/nonexistent values (NOT zero! NOT blank!)

    •See also: Use of NULL value in Voyager Prepackaged Access Reports

    •Not used for BLOB queries, which return string values; in BLOB queries, use "" to find empty string values

    clipboard_ed5dc53f9569135bf35e37f9644c49c7c.png

    The above query is an example of an LC Class Shelf List.  In addition to showing use of "Is Null" it also shows the use of a subquery and the "Between" operator.

    <>

    Not equal to

    clipboard_eadb648484311f270fbf431c911aa760a.png

    The above query excludes closed patterns (PREDICT field value of "S").

    The Not operator can also be used (e.g. Not "S")

    In( ) condition

    •Reduces need for multiple OR conditions in a SELECT statement

    •Example: In("30","21","6")


    clipboard_e859014d830536e5aad44cd8698365b92.png

    Note that the above query is also an example where we join fields with different names.

    Mid( ) function

    •Extracts a substring from a string

    •When using Mid() add one (1) to the byte position (MARC standard starts counting from zero, but MS Access begins counting from one).

    •See also: Viewing the added and built-in Voyager Prepackaged Access Reports functions

    •Example: Mid(NETWORK_NUMBER,8,Len(NETWORK_NUMBER)-7)

    clipboard_e22d7f4391ff8b1eb9c43ef59763a4b4b.png

    The above query finds records where the 008/23 - Form of Item - has no value specified:

    clipboard_e30c0f27b98600f1eef7d98e424f3dbf9.png

    It also shows using Leader/07 ("BIB_FORMAT")

    clipboard_edf95edf4966c0cb3d2847567335e52a9.png

    Additional Information

    Constructing custom SQL queries by request and troubleshooting unexpected results from customer-created SQL queries falls outside the scope of Support. The above has been posted for informational purposes.  Voyager-L and Developer Network are useful resources for finding helpful custom SQL or obtaining assistance from peers in troubleshooting custom queries.

     

    • Article last edited: 21-Jun-2021