Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    SQL to find patrons without email addresses

    • 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
    //Feedback