Scripts to find Bibs with no ADM or Bibs with HOL but no ADM

Description

In migrating to Alma (or other ILS), we need to create an ADM record for all bib records which lack one.  How can we do this?

Resolution

This hol-no-adm.sql script was used to get all non-deleted xxx01 Bib records which had a connected xxx60 HOL record but no xxx50 ADM record: 

SET LINESIZE 14
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET ECHO OFF

spool hol-no-adm.lst

select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z00_doc_number from z00, Z103 where z00_doc_number in (select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'XXX60')
minus select substr (Z103_REC_KEY_1,6,9) from z103 where z103_lkr_type = 'ADM'
minus select z13_rec_key from z13 where Z13_TITLE is null;

spool off
exit

It would be executed like this:

> s+ xxx01
SQL> @hol-no-adm

     
This script was used to get all non-deleted xxx01 Bib records which had no xxx50 ADM record (where a z13u_user_defined_2 field was defined with the 3-character ADM code):

SET LINESIZE 14
SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET PAUSE OFF
SET ECHO OFF

spool no-adm.xxx.lst

select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z13u_rec_key from Z13U where z13u_user_defined_2 = 'XXX'
minus select z13_rec_key from z13 where Z13_TITLE is null
minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'SDW50';

spool off
exit

It would be executed like this:

> s+ xxx01
SQL> @o-adm.xxx

• Article last edited: 5-Dec-2017