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 find patrons without email addresses

    SQL to find patrons without email addresses

    1. Last updated
    2. Save as PDF
    3. Share
      1. Share
      2. Tweet
      3. Share
    1. Additional Information
    • Article Type: General
    • Product: Aleph
    • Product Version: 20

    Desired Outcome Goal:
    A list of patrons who have no email address.

    Procedure:
    Note: The z304 (Patron address) record is in the $usr_library and the z305 (Local patron) record is in the ADM library. If the site is a single-ADM installation and these are the same, then the "xxx50." can be omitted from the following SQL. Otherwise, change "xxx50" to the local ADM.

    To produce files containing the z304_rec_key (patron ID + sequence#) and patron name for:

    1. cases where the email address is absent from an xxx50 patron's z304_address_type 02 ("mailing address") record:

    > s+ <$usr_library>
    SQL>
    spool xxxxxx02.lst
    set echo off
    set heading off
    set pause off
    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique z304_rec_key, substr (z304_address,1,40) from z304, xxx.50.z305 where Z304_EMAIL_ADDRESS is null and z304_address_type = '02' and substr(z304_rec_key,1,12) in (select substr(z305_rec_key,1,12) from xxx50.z305) order by substr (z304_address,1,40);

    2. cases where the email address is absent from any of the xxx50 patron's address records:

    > s+ <$usr_library>
    SQL>
    spool xxxxxxany.lst
    set echo off
    set heading off
    set pause off
    select /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ unique z304_rec_key, substr (z304_address,1,40), z304_address_type from z304, xxx50.z305 where Z304_EMAIL_ADDRESS is null and substr(z304_rec_key,1,12) in (select substr(z305_rec_key,1,12) from xxx50.z305) order by substr (z304_address,1,40);

    Additional Information

    There is no GUI Service which performs this function.


    • Article last edited: 12/2/2013
    View article in the Exlibris Knowledge Center
    1. Back to top
      • SQL to find number of items in collections that were returned late
      • SQL to find sum of what patrons owe
    • Was this article helpful?

    Recommended articles

    1. Article type
      Topic
      Language
      English
      Product
      Aleph
    2. Tags
      1. 20
      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