get_patrons_with_no_email.sql

-- get patron's with no e-mail info 
-- (some of which have some odd things going on in the barcode field)

SELECT
'p' || r.record_num || 'a' AS patron,
b.index_entry as barcode,
r.creation_date_gmt as creation_date,
r.record_last_updated_gmt as record_last_updated,
r.previous_last_updated_gmt as previous_last_updated,
v.field_content as patron_email

FROM
sierra_view.record_metadata	as r

LEFT OUTER JOIN
sierra_view.phrase_entry	AS b
ON
  (b.record_id = r.id) AND (b.index_tag = 'b')

LEFT OUTER JOIN
sierra_view.varfield		as v
ON
  (v.record_id = r.id) AND (v.varfield_type_code = 'z')

WHERE
r.deletion_date_gmt is null -- check to make sure record has not been deleted
AND r.campus_code = '' -- check to make sure patron record is ours

AND r.id IN (
	SELECT
	-- 'p' || r.record_num || 'a' AS patron_record_num,
	r.id

	FROM
	sierra_view.record_metadata	AS r

	LEFT OUTER JOIN
	sierra_view.varfield		AS v
	ON
	  (v.record_id = r.id) AND (v.varfield_type_code = 'z')

	WHERE
	r.record_type_code = 'p'

	GROUP BY
	r.id

	HAVING
	count(v.field_content) < 1
)