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

Description

How can we locate yyy30 Course doc records which don't have any yyy50 item records linked to them?

Resolution

Course doc records can be linked to items either directly, via a type ADM z103 ("professors' copies"), or indirectly, via an xxx01 bib record which is linked to items.
This is discussed in the article "Bib, item, and course record connections". 

The following SQL can be used to find cases where a non-deleted yyy30 course doc record is not linked to any yyy50 item, either directly or indirectly:

>   s+ yyy30
SQL> select z00_doc_number from z00 minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'YYY50' minus select z13_rec_key from z13 where Z13_TITLE is null; 

  (Note:  the three alphabetic characters in the YYY50 library code must be *uppercase* in doing this SQL.)

(Note that the yyy30 course doc record *could* be linked to xxx50 and zzz50 items if the xxx01 bib record is linked to those items. But the preceding SQL intentionally excludes those cases.) 

The following SQL will include the deleted Course doc records (stub records containing a "DEL Y") which the preceding SQL excludes:

SQL>  select z00_doc_number from z00 minus select substr (Z103_REC_KEY_1,6,9) from z103 where Z103_LKR_LIBRARY = 'YYY50'; 

The article " SQL to locate BIB/HOL recs without items; BIBs without ADM; etc. *MASTER RECORD* " contains similar, related SQL queries.

Article last edited: 14-Mar-2017