SQL to Identify Non-Serial BIBs with More Than 1 HOLding

• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
I'm looking for advice on how to create a SQL statement that will enumerate non-serial BIBs that have more than 1 HOLding attached.
I've got snippets from KB postings to identify non-serial bibs: (select Z00R_DOC_NUMBER from z00r where Z00R_FIELD_CODE = 'FMT' and Z00R_TEXT != 'SE') and non-deleted records: (select z13_rec_key from z13 where Z13_TITLE is not null), but I"m missing the piece that would limit the BIBs returned to only those with more than one HOLding.

Resolution:
Here is some SQL that does what you want. It has 2 main condition sections.
* The first includes only non-serial records and is just the code that you supplied for that.
* The second identifies records that have multiple HOL records linked by GROUPing HOLdings record links on their System Numbers and looking for those with a COUNT greater than 1.
I ran this on our USM01 library and it appears to work. Please let us know if you have further problems.

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'
)
AND Z00_DOC_NUMBER IN
(
SELECT
SUBSTR(Z103_REC_KEY_1,6,9)
FROM
Z103
WHERE SUBSTR(Z103_REC_KEY_1,4,2) = '01'
AND Z103_LKR_TYPE LIKE 'HOL'
GROUP BY
SUBSTR(Z103_REC_KEY_1,6,9)
HAVING COUNT(*) > 1
);

• Article last edited: 10/8/2013