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);