Evaluating patron IDs for choice of Alma primary patron ID

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