SQL to locate items with z30_hol_doc_number which bib record isn't actually linked to

Description

We have found cases where an item shows an HOL link but its bib record isn't actually connected to that (or any) HOL record.  We see this message in the GUI:  “Item, HOL linked to different Bibs”.    Can we get a list of such cases?

Resolution

The following SQL shows cases of items whose Z30_HOL_DOC_NUMBER_X shows a connection to an HOL record but whose bib record is not connected to any HOL record (-- change "xxx" to your local code --): 

SQL> select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ substr (z30_rec_key,1,9) from xxx50.z30 where Z30_HOL_DOC_NUMBER_X ^= '000000000' 
minus select substr (a.z103_rec_key,6,9) from xxx01.z103 a, xxx01.z103 b, xxx50.z30 where 
a.z103_lkr_type = 'ADM' and substr (a.z103_rec_key,6,9) = substr (z30_rec_key,1,9) and 
b.z103_lkr_type = 'HOL' and substr (b.z103_rec_key_1,6,9) = substr (a.z103_rec_key_1,6,9); 

This can be checked by taking one of the ADM numbers from the output (015057102, in this case) and plugging it into util f/3/12 like this: 

ENTER HOME LIBRARY : xxx50 
ENTER DOC-NUMBER : 015057102 
XXX01 015056867 ADM 
END 

and then taking the bib# and checking for xxx60 HOL records:

ENTER HOME LIBRARY : xxx01 
ENTER DOC-NUMBER : 015056867 
XXX50 015057102 ADM 
END 

If the Bib the ADM is connected to were linked to an HOL record, it would appear as an "XXX60 nnnnnnnnn HOL" line in the second util f/3/12. 

Looking at item 015057102-10 we see that it thinks it's linked to HOL record 020915482: 

02 z30_rec_key \ 
03 doc_number ................015057102 
03 item_sequence .............000010 
... 
03 hol_doc_number ............020915482 

But, checking xxx60 HOL 020915482 in util f/4 we see that it is linked to Bib 015056867: 

LKR L $$aHOL$$lXXX01$$b015056867