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.