Commonly used SQL Keywords in Voyager Prepackaged Access Reports
- 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