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. Locating HOL records with no items when other HOLs for same bib *do* have items

    Locating HOL records with no items when other HOLs for same bib *do* have items

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Description
    2. Resolution

     

    • Product: Aleph
    • Product Version: 20, 21, 22, 23
    • Relevant for Installation Type: Dedicated-Direct, Direct, Local, Total Care

     

    Description

    We want to locate and delete HOL records which have no items linked to them when the bib records they are linked to have other HOL records linked to them which *do* have items.

    Resolution

    The following SQL script can be used to locate such HOL records:

    set echo off
    set pause off
    set term off
    set show off
    set feed off
    set verify off
    set heading off
    set pagesize 58

    spool z103-no-z30-link

    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique substr(a.z103_rec_key_1,6,9), substr(a.z103_rec_key,6,9)  from xxx01.z103 a, xxx01.z103 b where
    a.z103_lkr_type = 'HOL' and substr(a.z103_rec_key,6,9) not in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
    a.z103_lkr_type = 'HOL' and substr(b.z103_rec_key,6,9) in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
    substr(a.z103_rec_key_1,6,9) = substr(b.z103_rec_key_1,6,9) order by substr(a.z103_rec_key_1,6,9), substr(a.z103_rec_key,6,9);

    spool off
    exit


    The output from the  above script has the bib record number in the first column and the xxx60 HOL record number in the second. This makes it easier to analyze the records.

    But in order to actually delete these HOL records you will need a file with just the HOL record number.  The following SQL SELECT will give you that:

    SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique substr(a.z103_rec_key,6,9)  from xxx01.z103 a, xxx01.z103 b where
    a.z103_lkr_type = 'HOL' and substr(a.z103_rec_key,6,9) not in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
    a.z103_lkr_type = 'HOL' and substr(b.z103_rec_key,6,9) in (SELECT /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ z30_hol_doc_number_x from xxx50.z30) and
    substr(a.z103_rec_key_1,6,9) = substr(b.z103_rec_key_1,6,9) order by substr(a.z103_rec_key,6,9);

    Then you would need to take this output and append "XXX60" to each line, as described in the article "  Appending library to each line in file of record numbers (make "p_ret_01 format"  ".

    Then you would use that as input to the print-03 Service to create an export file with these records in it and use that file as input to the manage-18
    Service (using its "Delete a record" function).  See the article " Batch HOL record deletion " for information on this.

     


    • Article last edited: 8-Mar-2018
    View article in the Exlibris Knowledge Center
    1. Back to top
      • Locating bib records with a 130 field
      • Locating in-transit items
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. contype:kba
      2. Prod:Aleph
    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