Skip to main content
ExLibris

Knowledge Assistant

BETA
 
  • Subscribe by RSS
  • Back
    Aleph

     

    Ex Libris Knowledge Center
    1. Search site
      Go back to previous article
      1. Sign in
        • Sign in
        • Forgot password
    1. Home
    2. Aleph
    3. Knowledge Articles
    4. SQL to Identify Non-Serial BIBs with More Than 1 HOLding

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

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    No headers
    • 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
    View article in the Exlibris Knowledge Center
    1. Back to top
      • SQL to get patrons with most loans
      • SQL to list patron barcodes/addresses for a particular patron type
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 16.02
      2. contype:kba
      3. Prod:Aleph
      4. Type:General
    1. © Copyright 2025 Ex Libris Knowledge Center
    2. Powered by CXone Expert ®
    • Term of Use
    • Privacy Policy
    • Contact Us
    2025 Ex Libris. All rights reserved