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