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

    Voyager to Alma Migration Query: Identify Duplicate Call Numbers (Pass-through query)

    Created By: Laura Guy (contact)
    Created on: 11/27/2020

    When migrating from Voyager to Alma it may be helpful to identify and review duplicate call numbers.

    This Pass-Through SQL Query (it will not run as is as a normal Access Query) finds pairs of records with the same call number, but different BIB IDs.  It compares the normalized call number, the way Voyager indexes them.  The query also checks that one MFHD_ID is greater than the other, so that each pair only shows up once in the results.  Bound-withs (different BIB, same holdings) are excluded.

    Note that this query checks call number type.  It is set to look at LC, but can be altered to look at Dewey.

      bt1.bib_id as bib1
    , bt2.bib_id as bib2
    , mm1.display_call_no
    , bt1.title_brief as title_1
    , bt2.title_brief as title_2
    from mfhd_master mm1
    inner join bib_mfhd bm1 on mm1.mfhd_id = bm1.mfhd_id
    inner join bib_text bt1 on bm1.bib_id = bt1.bib_id
    inner join mfhd_master mm2 on mm1.normalized_call_no = mm2.normalized_call_no
    inner join bib_mfhd bm2 on mm2.mfhd_id = bm2.mfhd_id
    inner join bib_text bt2 on bm2.bib_id = bt2.bib_id
    where bm1.bib_id != bm2.bib_id
    and bm1.mfhd_id > bm2.mfhd_id
    and mm1.call_no_type = '0' --change this to 1 if you use Dewey
    order by mm1.normalized_call_no;

    For other queries that may be useful, see my "Surviving Almanado: tips for a successful pre-implementation" presentation's Accompanying Materials.
    Posted as is. If you need assistance in running custom SQL queries in Prepackaged Access Reports, consult the Voyager Customer Listserv.