Skip to main content
Ex Libris Knowledge Center

SQL to locate orphaned item records

  • Article Type: General
  • Product: Aleph
  • Product Version: 20
  • Relevant for Installation Type: Dedicated-Direct; Direct; Local; Total Care

Desired Outcome Goal:
SQL locates cases of "orphaned" z30 item records (z30 records whose ADM record has been deleted)

Procedure:
The following SQL can be used to locate item records not connected to any ADM (or to any BIB record):

SQL> select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_rec_key, z30_barcode from z30 where substr (z30_rec_key,1,9) not in (select substr (z103_rec_key,6,9) from z103 where z103_rec_key like 'xxx50%');

<where "xxx50" is the ADM library>

These items are of no practical value. If there are not so many, they can be left for historical/reference purposes. If they are more numerous, consider the info in Additional Information, below, and, if apporopriate:
1. delete them with the item-11 ("Delete Item Records") Service; or, if that doesn't work,
2. they can be deleted via SQL.

Additional Information

This should *not* be happening.  The problem may be connected to running of the item-11 service ("Delete Item Records") with "Record Management"  set to "Delete".

If there are many such items and it's unclear how they got orphaned, contact Ex Libris Support.

Category: Cataloging


  • Article last edited: 8/26/2015