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

    Voyager to Alma Migration Query: Item Types in Use as Temporary or Permanent Types

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



    When migrating from Voyager to Alma, you may want to simplify your item types.  This query and subquery work together to count the occurrences of item type codes as temporary or permanent item types.

    This query, which can be run using Voyager's Prepackaged Access Reports, shows item type code, item type name, permanent or temporary, and number of uses.

    Subquery: This subquery lists item_ids, item type ids, and a field that shows whether the item type is permanent or temporary.  Save it under the name “Item Types in Use Subquery” but don’t run it:

    SELECT ITEM.ITEM_ID, ITEM.ITEM_TYPE_ID, "Perm"
    AS Perm_Temp FROM ITEM
    UNION ALL
    SELECT ITEM.ITEM_ID, ITEM.TEMP_ITEM_TYPE_ID, "Temp"
    AS Perm_Temp FROM ITEM;

     


    Main Query: This compares your list of item types to the results of the subquery and counts the occurrences.  Save it under any name you wish and run it.

    SELECT ITEM_TYPE.ITEM_TYPE_CODE, ITEM_TYPE.ITEM_TYPE_NAME,
    [Item Types in Use Subquery].Perm_Temp,
    Count([Item Types in Use Subquery].ITEM_TYPE_ID) AS Count
    FROM ITEM_TYPE LEFT JOIN [Item Types in Use Subquery]
    ON ITEM_TYPE.ITEM_TYPE_ID = [Item Types in Use Subquery].ITEM_TYPE_ID
    GROUP BY ITEM_TYPE.ITEM_TYPE_CODE, ITEM_TYPE.ITEM_TYPE_NAME,
    [Item Types in Use Subquery].Perm_Temp
    ORDER BY ITEM_TYPE.ITEM_TYPE_CODE, [Item Types in Use Subquery].Perm_Temp;

     

    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.