Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    SQL to locate BIB/HOL recs without items; BIBs without ADM; etc. *MASTER RECORD*

    • Article Type: Q&A
    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct; Direct; Local;

    Question

    Is there a way to generate a list of BIB records without holdings and/or holdings without items? Etc.

    Answer

    The following SQL can be used.   (Note:  "exu01", "exu50", etc., need to be changed to your local library codes.)

    SQL for bib records:

    1. To get non-deleted bib records which have neither a linked ADM or HOL record:
    SQL-EXU01> select z00_doc_number from z00 minus select substr (Z103_REC_KEY_1,6,9) from z103 minus select z13_rec_key from z13 where Z13_TITLE is null;

    2. To get non-deleted bib records which do not have a linked ADM record:
    SQL-EXU01> select z00_doc_number from z00 minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY like '%50' minus select z13_rec_key from z13 where Z13_TITLE is null;

    3. To get non-deleted bib records which do not have a linked HOL record:
    SQL-EXU01> select z00_doc_number from z00 minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'EXU60' minus select z13_rec_key from z13 where Z13_TITLE is null;

    3a. To get non-deleted *serial* bib records which do not have a linked HOL record:
    SQL-EXU01> select z00_doc_number from z00 where z00_doc_number in (select Z00R_DOC_NUMBER from z00r where Z00R_FIELD_CODE = 'FMT' and Z00R_TEXT = 'SE') minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'EXU60' minus select z13_rec_key from z13 where Z13_TITLE is null;

    3b. To get non-deleted bib records with an HOL record but no ADM (from KB 16384-10738: Report of bib records with HOL record but no items):
    SQL-ABC01> select z00_doc_number from z00 where z00_doc_number in (select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'ABC60')
    minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY like '%50'
    minus select z13_rec_key from z13 where Z13_TITLE is null;


    Note: The qualifier: "minus select z13_rec_key from z13 where Z13_TITLE is null" is added to the query to omit deleted bib records from the results.

    * To get bib records with more than one HOL or more than one ADM linked to them, see KB 4059 (SQL for BIB Records Linked to Multiple HOLs or Multiple ADMs).

    SQL for ADM, HOL, and item records:

    4. To get non-deleted HOL records which do not have any items linked to them:
    SQL-EXU50> select substr (z103_rec_key,6,9) from exu60.z103 where substr (z103_rec_key,1,5) = 'EXU60' minus select Z30_HOL_DOC_NUMBER_X from z30;

    5. To get non-deleted ADM records which do not have any items linked to them:
    SQL-EXU50> select Z00_DOC_NUMBER from exu50.z00 where Z00_DOC_NUMBER in (select substr (Z103_REC_KEY,6,9) from z103 where Z103_REC_KEY like 'EXU50%') minus select substr (Z30_REC_KEY,1,9) from exu50.z30;

    6. To get non-deleted ADM records which do not have any orders linked to them:
    SQL-EXU50> select Z00_DOC_NUMBER from exu50.z00 where Z00_DOC_NUMBER in (select substr (Z103_REC_KEY,6,9) from z103 where Z103_REC_KEY like 'EXU50%') minus select substr (Z68_REC_KEY,1,9) from exu50.z68;

    7. To get item records which are not linked to any HOL record:
    SQL-EXU50> select Z30_REC_KEY from exu50.z30 where Z30_HOL_DOC_NUMBER_X = '000000000';

    7a.  The article " SQL to locate items with z30_hol_doc_number which bib record isn't actually linked to " describes the case where the z30_hol_doc_number_x points to a deleted or non-existent HOL record. 

    7b. To get item records connected to the same ADM, with the same sublibrary code but a different collection:
    SQL> select z30_rec_key from exu50.z30 where z30_rec_key like '000123456%' and z30_sub_library = 'xxxxx' and z30_collection ^= 'yyyyy';
    <where xxxxx is the 000123456's sublibrary and yyyyy is 000123456's Collection>

    7c. To locate "orphaned" items (items not connected to any adm or bib record), see KB 16384-52321 ( SQL to locate orphaned item records ).

    7d. To locate item/bib/HOL records which cause the  GUI message: "Item, HOL linked to different Bibs" .

    8. To locate ANA/ITM links pointing to deleted item records, see KB 4125 (Deleting item doesn't delete ITM z103 records) (Its seems that this SQL was lost at some point....)

    9. To locate bogus ADM links (links to ADM records which don't actually exist), see KB 8192-2745 ( Error Message: Item does not exist (Remote Service Error: c0174) ). 

    10. To get z68 (order) records for which there is no corresponding ADM record, see KB 8192-953 ( Order record without ADM? )

    11. To get to non-deleted bib records with an HOL record but no z30 (item) record, see KB 16384-10738 ( Report of bib records with HOL record but no items )

    12. The above queries retrieve record keys only. To get the title in addition to the rec-key, consult KB 16384-48993 ( Titles (not just rec-key's) of bibs without holdings ) 

     

    13.  To get Course doc records which don't have any items linked to them see:  How to find Course doc records which have no items linked to them

    Additional Information

     "Why run these reports?  what do you do with the results?"

    Answer:   Some customers may want to identify bib records which have no associated ADM record, no associated items, etc.
    There is no *requirement* that such SQL be run.  This article makes it available to sites who want to check for these conditions.

    Smart Links to Related Articles:
    KB 16384-52321 SQL to locate orphaned item records
    KB 4125  [Unavailable]
    KB 8192-2745 Error Message: Item does not exist (Remote Service Error: c0174)
    KB 8192-953  Order record without ADM?
    KB 16384-10738  Report of bib records with HOL record but no items
    KB 16384-48993 Titles (not just rec-key's) of bibs without holdings

                                How to find Course doc records which have no items linked to them



     

    Category: System Management (500)


    • Article last edited: 7/16/2015