How to find duplicate HOL records (for the same sublibrary/collection)

Description

How to find cases of item records connected to the same bib record, with the same sublibrary/collection, which have multiple HOL records (with the same 852 $$b$$c). 

Resolution

s+ xxx50

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

spool adms-with-multiple-hols.lst

select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique substr (a.z30_rec_key,1,9) from z30 a, z30 b
where substr (a.z30_rec_key,1,9) = substr (b.z30_rec_key,1,9) and
a.z30_sub_library = b.z30_sub_library and
a.z30_collection = b.z30_collection and
a.z30_hol_doc_number_x ^= b.z30_hol_doc_number_x and
a.z30_hol_doc_number_x ^= '000000000'  and
b.z30_hol_doc_number_x ^= '000000000'
order by substr (a.z30_rec_key,1,9);

spool off

exit
 

• Article last edited: 6-Jun-2017