Patron email via SQL - Innovative ILS

This only returns records if email exists:  (From Sierra Listserv, David Noe)

SELECT svv.field_content,

svn.last_name,

svn.first_name

FROM sierra_view.patron_record svp

LEFT JOIN sierra_view.record_metadata svm ON svp.id=svm.id

LEFT JOIN  sierra_view.varfield svv

ON  svp.record_id = svv.record_id

LEFT JOIN sierra_view.patron_record_fullname svn ON  svp.record_id = svn.patron_record_id

WHERE svm.record_type_code='p'

AND svv.varfield_type_code = 'z';

-----------------------------------

This returns all patrons, even if email is blank:


In our system, the patron's email is in varfield z.  This can be joined to the patron view. Here's an example where we're looking for patrons with incorrectly formatted email addresses:

SELECT
  v.home_library_code AS "home_agency",
  'p' || v.record_num ||'a' AS "patron_no",
  n.first_name,
  n.last_name,
  RTRIM (e.field_content) AS "email"

FROM
   sierra_view.patron_record p
  JOIN sierra_view.patron_view v ON p.id = v.id
  JOIN sierra_view.patron_record_fullname n ON p.id = n.patron_record_id
  LEFT JOIN sierra_view.varfield e ON e.record_id = p.id AND e.varfield_type_code = 'z'

Gerri Moeller