Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Voyager

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Voyager
    3. Knowledge Articles
    4. Commonly used SQL Keywords in Voyager Prepackaged Access Reports

    Commonly used SQL Keywords in Voyager Prepackaged Access Reports

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Question
    2. Answer
      1. Access SQL Example
    3. Additional Information
    • Product: Voyager
    • Relevant for Installation Type: Multi-Tenant Direct, Dedicated-Direct, Local, TotalCare

     

    Question

    What are some of the more commonly used SQL Keywords found in Voyager Prepackage Access Reports?

    Answer

    SELECT List the fields you want
    DISTINCT Keep only unique values
    FROM List the tables from which to get data
    INNER JOIN Standard join -- fields in both tables are equal
    OUTER JOIN Special join -- if no matching value in 2nd table, keep record anyway
    INTO Make a table from the results of your query
    AS Rename a field or a table within the query
    ORDER BY Sort the results by these fields, left to right
    ASC Do the sort in ascending order (default)
    DESC Do the sort in descending order
    GROUP BY Aggregate the records where these fields are the same
    WHERE Keep only records that match these criteria
    AND Additional WHERE criteria
    HAVING When you use GROUP BY, the WHEREs turn to HAVINGs !?!
    UNION Combine two tables into one long table
    UNION ALL Do a UNION, but don’t delete duplicate records (this is a “gotcha”)

    Access SQL Example

    SELECT DISTINCT 
    PATRON.LAST_NAME AS TOO_MANY, 
    PATRON.FIRST_NAME, CIRC_TRANSACTIONS.CURRENT_DUE_DATE
    FROM PATRON INNER JOIN CIRC_TRANSACTIONS ON PATRON.PATRON_ID = CIRC_TRANSACTIONS.PATRON_ID
    WHERE (((CIRC_TRANSACTIONS.[CURRENT_DUE_DATE])<Date()) AND ((CIRC_TRANSACTIONS.[OVERDUE_NOTICE_COUNT])>"1"))
    ORDER BY CIRC_TRANSACTIONS.CURRENT_DUE_DATE DESC;

     

    Additional Information

    The Voyager server uses SQL*Plus, which is different from Access SQL.  They are not compatible.

     

    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: 03-Mar-2021
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Command searches on Chinese words made up of several characters fail
      • Commonly used SQL operators in Voyager Prepackaged Access Reports
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Content Type
      Knowledge Article
      Language
      English
      Product
      Voyager
    2. Tags
      This page has no tags.
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved