How to query Voyager for list of print journals for vendor?

Last updated
Save as PDF
Share
1. Share
2. Tweet
3. Share

Article Type: General
Product: Voyager

Question

How can we provide a list of print journals in Voyager to another vendor (i.e., Serials Solutions)?

Answer

Utilizing MS Access in Voyager, you can create a file that contains the titles and ISSNs of print serials. Generate this report from Voyager and send the file to the vendor.

Open Reports.mdb (Reports.mdb must be linked to Voyager database via ODBC)
In Queries, create new query in SQL view.
Add following query to SQL view:

SELECT BIB_TEXT.ISSN
, BIB_TEXT.TITLE
, BIB_TEXT.PUBLISHER
, LOCATION.LOCATION_NAME
, GetFieldAll([blob],"866") AS 866
, GetFieldAll([blob],"853") AS 853
, GetFieldAll([blob],"863") AS 863
FROM
(((BIB_MFHD INNER JOIN BIB_TEXT ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID)
INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID)
INNER JOIN LOCATION ON MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID)
INNER JOIN MFHDBlob ON MFHD_MASTER.MFHD_ID = MFHDBlob.MFHD_ID
WHERE (((MFHD_MASTER.SUPPRESS_IN_OPAC)<>'Y') AND ((Mid([bib_format],2,1))='s'));

Click Run.
Export to desired format (Excel, Text file, etc.)

Additional Information

The above query uses BLOB functions and can take a very long time to run.

A different query was submitted by a customer. It is included below. It provides a list of ISSNs and titles for all serials (bib level 's') in the collection, omitting records whose MFHD is marked as suppressed in the OPAC. Records without ISSNs are excluded. While it does not use the BLOB function, it can still take a long time to run:

SELECT DISTINCT utf8to16([bib_text].[ISSN]) AS ISSN,
utf8to16([bib_text].[TITLE_BRIEF]) AS TITLE_BRIEF
FROM (BIB_MFHD INNER JOIN BIB_TEXT
ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID)
INNER JOIN MFHD_MASTER ON BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID
WHERE (((utf8to16([bib_text].[ISSN])) Is Not Null
And (utf8to16([bib_text].[ISSN]))<>" ")
AND ((MFHD_MASTER.SUPPRESS_IN_OPAC)<>'Y')
AND ((Mid([bib_format],2,1))='s'));

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: 05-Mar-2015