Skip to main content
  • Subscribe by RSS

    Ex Libris Knowledge Center

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


    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care



    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?


    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 DOC-NUMBER : 015057102 
    XXX01 015056867 ADM 

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

    ENTER DOC-NUMBER : 015056867 
    XXX50 015057102 ADM 

    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 

    Additional Information

    The article  ' GUI message: "Item, HOL linked to different Bibs" ' is about these specific instances, which are a subset of the instances 

    • Article last edited: 3-Jan-2018
    • Was this article helpful?