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

    Voyager to Alma Migration Query: Course Reserves Cleanup

    Created By: Laura Guy (contact)
    Created on: 9/18/2020



    When migrating from Voyager to Alma it may be desirable to review and cleanup Course Reserves data.  The following queries can assist you with that.

    These queries can be run using Voyager Prepackaged Access Reports.  They each list various aspects of Voyager Course Reserves information.

    Note that Course Reserves data are often messy and complicated.  You should review the results of these queries carefully as there might be innacurracies due to data vagaries.

     

    Active Reserve Lists with Course Name and Titles

    ​SELECT RESERVE_LIST.LIST_TITLE, utf8to16([bib_text].[TITLE]) AS Title,
    COURSE.COURSE_NAME
    FROM ((((RESERVE_LIST
    INNER JOIN RESERVE_LIST_ITEMS ON RESERVE_LIST.RESERVE_LIST_ID =
    RESERVE_LIST_ITEMS.RESERVE_LIST_ID)
    INNER JOIN ITEM ON RESERVE_LIST_ITEMS.ITEM_ID = ITEM.ITEM_ID)
    INNER JOIN RESERVE_LIST_COURSES ON RESERVE_LIST.RESERVE_LIST_ID =
    RESERVE_LIST_COURSES.RESERVE_LIST_ID)
    INNER JOIN COURSE ON RESERVE_LIST_COURSES.COURSE_ID = COURSE.COURSE_ID)
    INNER JOIN (BIB_TEXT INNER JOIN BIB_ITEM ON BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID)
    ON ITEM.ITEM_ID = BIB_ITEM.ITEM_ID
    ORDER BY RESERVE_LIST.LIST_TITLE,
    utf8to16([bib_text].[TITLE]), COURSE.COURSE_NAME;

     

    All Physical Items on both Active and Expired Reserve Lists

    Subquery: Save this Subquery with the name AllNonEItemsOnReserveSubquery and DO NOT run it.

    SELECT RESERVE_LIST.RESERVE_LIST_ID, ITEM.ITEM_ID,
    RESERVE_LIST.LIST_TITLE, RESERVE_LIST.EFFECT_DATE,
    RESERVE_LIST.EXPIRE_DATE, utf8to16([bib_text].[TITLE]) AS Title,
    utf8to16([bib_text].[AUTHOR]) AS Author,
    ITEM_BARCODE.ITEM_BARCODE, RESERVE_LIST_COURSES.DEPARTMENT_ID,
    RESERVE_LIST_COURSES.INSTRUCTOR_ID, RESERVE_LIST_COURSES.COURSE_ID
    FROM (BIB_TEXT INNER JOIN (((MFHD_MASTER INNER JOIN MFHD_ITEM
    ON MFHD_MASTER.MFHD_ID = MFHD_ITEM.MFHD_ID)
    INNER JOIN (ITEM INNER JOIN ((RESERVE_LIST
    LEFT JOIN RESERVE_LIST_COURSES
    ON RESERVE_LIST.RESERVE_LIST_ID = RESERVE_LIST_COURSES.RESERVE_LIST_ID)
    INNER JOIN RESERVE_LIST_ITEMS
    ON RESERVE_LIST.RESERVE_LIST_ID = RESERVE_LIST_ITEMS.RESERVE_LIST_ID)
    ON ITEM.ITEM_ID = RESERVE_LIST_ITEMS.ITEM_ID)
    ON MFHD_ITEM.ITEM_ID = ITEM.ITEM_ID) INNER JOIN BIB_MFHD
    ON MFHD_MASTER.MFHD_ID = BIB_MFHD.MFHD_ID)
    ON BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID) LEFT JOIN ITEM_BARCODE
    ON ITEM.ITEM_ID = ITEM_BARCODE.ITEM_ID
    ORDER BY RESERVE_LIST.LIST_TITLE, RESERVE_LIST.EXPIRE_DATE;

    Main Query: Save this Main query with the name AllNonEItemsOnReserveMainQuery and DO run it.

    SELECT AllNonEItemsOnReserveSubquery.RESERVE_LIST_ID,
    AllNonEItemsOnReserveSubquery.ITEM_ID,
    AllNonEItemsOnReserveSubquery.LIST_TITLE,
    AllNonEItemsOnReserveSubquery.EFFECT_DATE,
    AllNonEItemsOnReserveSubquery.EXPIRE_DATE,
    AllNonEItemsOnReserveSubquery.Title,
    AllNonEItemsOnReserveSubquery.Author,
    AllNonEItemsOnReserveSubquery.ITEM_BARCODE,
    COURSE.COURSE_NAME, COURSE.COURSE_NUMBER,
    DEPARTMENT.DEPARTMENT_NAME, DEPARTMENT.DEPARTMENT_CODE,
    INSTRUCTOR.LAST_NAME AS [Instructor LAST_NAME],
    INSTRUCTOR.FIRST_NAME AS [Instructor FIRST_NAME]
    FROM ((AllNonEItemsOnReserveSubquery LEFT JOIN DEPARTMENT
    ON AllNonEItemsOnReserveSubquery.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)
    LEFT JOIN INSTRUCTOR
    ON AllNonEItemsOnReserveSubquery.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID)
    LEFT JOIN COURSE
    ON AllNonEItemsOnReserveSubquery.COURSE_ID = COURSE.COURSE_ID
    ORDER BY AllNonEItemsOnReserveSubquery.LIST_TITLE,
    AllNonEItemsOnReserveSubquery.EXPIRE_DATE, COURSE.COURSE_NAME,
    COURSE.COURSE_NUMBER, INSTRUCTOR.LAST_NAME, INSTRUCTOR.FIRST_NAME;

     

    All eItems on both Active and Expired Reserve Lists

    Subquery: Save the subquery with the name AllEItemsOnReserveSubquery, and DO NOT run it.

    SELECT RESERVE_LIST.LIST_TITLE AS [Reserve List Name],
    RESERVE_LIST.CREATE_DATE, RESERVE_LIST.EFFECT_DATE,
    RESERVE_LIST.EXPIRE_DATE, BIB_TEXT.BIB_ID, EITEM.MFHD_ID,
    EITEM.EITEM_ID, BIB_TEXT.TITLE_BRIEF,
    RESERVE_LIST.RESERVE_LIST_ID,
    RESERVE_LIST_COURSES.DEPARTMENT_ID,
    RESERVE_LIST_COURSES.INSTRUCTOR_ID,
    RESERVE_LIST_COURSES.COURSE_ID,
    RESERVE_LIST_COURSES.SECTION_ID
    FROM (((EITEM INNER JOIN (BIB_MFHD
    INNER JOIN BIB_TEXT ON BIB_MFHD.BIB_ID = BIB_TEXT.BIB_ID)
    ON EITEM.MFHD_ID = BIB_MFHD.MFHD_ID) INNER JOIN RESERVE_LIST_EITEMS
    ON EITEM.EITEM_ID = RESERVE_LIST_EITEMS.EITEM_ID)
    INNER JOIN RESERVE_LIST
    ON RESERVE_LIST_EITEMS.RESERVE_LIST_ID = RESERVE_LIST.RESERVE_LIST_ID)
    LEFT JOIN RESERVE_LIST_COURSES
    ON RESERVE_LIST.RESERVE_LIST_ID = RESERVE_LIST_COURSES.RESERVE_LIST_ID
    ORDER BY RESERVE_LIST.LIST_TITLE;

    Main Query: Save the main query with the name AllEItemsOnReserveQuery and DO run it.

    SELECT AllEItemsOnReserveSubquery.RESERVE_LIST_ID,
    AllEItemsOnReserveSubquery.[Reserve List Name],
    AllEItemsOnReserveSubquery.CREATE_DATE,
    AllEItemsOnReserveSubquery.EFFECT_DATE,
    AllEItemsOnReserveSubquery.EXPIRE_DATE,
    AllEItemsOnReserveSubquery.BIB_ID,
    AllEItemsOnReserveSubquery.MFHD_ID,
    AllEItemsOnReserveSubquery.EITEM_ID,
    AllEItemsOnReserveSubquery.TITLE_BRIEF,
    COURSE.COURSE_NAME, COURSE.COURSE_NUMBER,
    DEPARTMENT.DEPARTMENT_NAME, INSTRUCTOR.LAST_NAME
    AS [Instructor LAST_NAME], INSTRUCTOR.FIRST_NAME
    AS [Instructor FIRST_NAME]
    FROM ((AllEItemsOnReserveSubquery LEFT JOIN DEPARTMENT
    ON AllEItemsOnReserveSubquery.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID)
    LEFT JOIN COURSE
    ON AllEItemsOnReserveSubquery.COURSE_ID = COURSE.COURSE_ID)
    LEFT JOIN INSTRUCTOR
    ON AllEItemsOnReserveSubquery.INSTRUCTOR_ID = INSTRUCTOR.INSTRUCTOR_ID
    ORDER BY AllEItemsOnReserveSubquery.[Reserve List Name],
    AllEItemsOnReserveSubquery.EXPIRE_DATE, COURSE.COURSE_NAME,
    COURSE.COURSE_NUMBER, DEPARTMENT.DEPARTMENT_NAME,
    INSTRUCTOR.LAST_NAME, INSTRUCTOR.FIRST_NAME;

    For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.

    If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.




    • Was this article helpful?