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

    Evaluating patron IDs for choice of Alma primary patron ID

     

    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

     

    Description

    We want to see if the university ID (z308 type 03) might be used as the Alma primary patron ID.  

    Resolution

    Below is SQL showing z303 (global patron) and z308 (patron ID) relationships.  SQL's 8 and 9 just have the record ID.  If you want more columns to be selected, you can add them....  

     

    In any case, it would seem that, for employees and students, the choice of the university ID is best.  Outside borrowers (alumni, townspeople, etc.) wouldn't have a university ID, but they wouldn't be receiving updates from HR or Student records as the employees and students would, so there would be no need for a known, matchable ID for them.  I'd say it would be best to use the university ID as the primary ID for university-connected patrons and to use the Aleph patron ID (z303_rec_key, z308-KEY-TYPE 00), as the primary ID in the migration of other patrons (-- to make sure that they receive a unique value).  

     

    SQL's for evaluating patron ID's:


    1. Count of z303 (global patron) records:

    sys00@ALEPH20> select count(*) from z303;
    **** Hit return to continue ****

      COUNT(*)
    ----------
         78315


    2. Count of patrons who have z308 type 00 (Aleph patron ID):

    sys00@ALEPH20> select count(*) from z308 where z308_rec_key like '00%';
    **** Hit return to continue ****

      COUNT(*)
    ----------
         79289

    Thus, there are 974 patrons (79,289 - 78,315) who have a z308_rec_key type 00, but no z303 record.  These z308's are *not* functional.  A patron has to have both a z308_rec_key type 00  and a z303 in order to be functional.


    3. Count of patrons who have a z308 type 01 (barcode) record:

    sys00@ALEPH20> select count(*) from z308 where z308_rec_key like '01%';
    **** Hit return to continue ****

      COUNT(*)
    ----------
         79303

    Thus, there are 988 cases of barcode records without a z303 (global patron) record  (79,303 - 78,315). 
    These z308's are *not* functional.  A patron has to have both a z308_rec_key type 01 and a z303 in order to
    be functional.

     

    4. These are 36 patrons who have a z303 record but no z308_rec_key type 01.  These are invalid.  Every valid
    patron has to have a z308_rec_key type 01 (barcode):

    sys00@ALEPH20> select z303_rec_key from z303 minus select z308_id from z308 where z308_rec_key like '01%';
    **** Hit return to continue ****

    Z303_REC_KEY
    ------------
    DSU501217
    ID504852
    LTI000000059
    LTI000000063
    LTI000000494
    LTI000000518
    LTI000000947
    LTI000001092
    LTI000001104
    LTI000001214
    LTI000001332
    LTI000001498
    LTI000001602
    LTI000001604
    LTI000001744
    LTI000001908
    LTI000001994
    LTI000002006
    LTI000002016
    LTI000002026
    LTI000002096
    LTI000002184
    LTI000002319
    LTI000002371
    LTI000002424
    LTI000002425
    LTI000002430
    MKE000000622
    MKE000000803
    MKE000000837
    MKE000000955
    MKE439
    MKE440
    TODWA
    TSTIL
    WOL3572


    5. Count of patrons who have a type 03 z308 record:

    sys00@ALEPH20> select count(*) from z308 where z308_rec_key like '03%';
    **** Hit return to continue ****

      COUNT(*)
    ----------
         69959


    6. Count of patrons who have more than one type 03 z308 record: 69:

    sys00@ALEPH20> select z308_id from z308 where z308_rec_key like '03%' group by z308_id having count(*) > 1;
    ...
    69 rows selected.

     

    7. Count of patrons who have an Aleph Global patron record (z303) but no type 03 z308 record:

          8,320  (78,315 - 36 - 69,959)  (from #1 and #5 above)


    8. List of patrons who have a type 03 z308 record:

    select z308_rec_key, z303_name from z308, z303 where z303_rec_key = z308_id and z308_rec_key like '03%';


    9. List of of patrons who have no type 03 z308 record:

    select  z303_rec_key from z303 minus select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z308_id from z308, z303 where z303_rec_key = z308_id and z308_rec_key like '03%';

     

     


    • Article last edited: 10-Feb-2017