This is an old revision of the document!
This process is used to move sets of bib, holdings, and item records from one ADM to the other. The first project this process was created for was to move Smith College Microfilm into the UMass ADM and then to assimilate it into our Microfilm collections.
This step was accomplished using the following SQL code (“–” indicates a comment):
– load bib nbrs into rick_smtmicro_1
– get adm nbr
alter table rick_smtmicro_1 add adm_nbr char(9) ; update rick_smtmicro_1 set adm_nbr = ( select substr(z103_rec_key,6,9) from fcl01.z103 where substr(z103_rec_key,1,5) = 'UMA50' and z103_rec_key_1 = 'FCL01' || substr(bib_nbr,1,9) ) ; commit ;
– parse note field
drop table rick_smtmicro_2 ; create table rick_smtmicro_2 as select z30_rec_key, z30_note_internal, substr(regexp_substr(z30_note_internal,'\$\$a[^\$\s]+'),4,20) a, substr(regexp_substr(z30_note_internal,'\$\$b[^\$\s]+'),4,20) b, substr(regexp_substr(z30_note_internal,'\$\$i[^\$\s]+'),4,20) i, substr(regexp_substr(z30_note_internal,'\$\$j[^\$\s]+'),4,20) j, substr(regexp_substr(z30_note_internal,'\$\$k[^\$\s]+'),4,20) k, z30_enumeration_a, z30_enumeration_b, z30_chronological_i, z30_chronological_j, z30_chronological_k from rick_smtmicro_1, uma50.z30 where z30_rec_key like adm_nbr || '%' ;
– move values to various item fields
update uma50.z30 z set (z30_enumeration_a, z30_enumeration_b, z30_chronological_i, z30_chronological_j, z30_chronological_k) = ( select a, b, i, j, k from rick_smtmicro_2 r where r.z30_rec_key = z.z30_rec_key ) where z.z30_rec_key in ( select s.z30_rec_key from rick_smtmicro_2 s ) ; commit ;