(from Laura Daniels)
Getting Data that is Null when it is not stored as Null
I was looking for instance records with certain parameters which include the absence of an identifier with a particular type. (I didn't want all records that lacked identifiers, I wanted all records that lacked identifiers with the type "OCLC.") It became apparent that using IS NOT NULL did not work for this situation. The last two lines of this query show the solution I found: selecting the record ids that are NOT IN a subquery that identifies the records that have an identifier of this type. I put this at the end of the query, so that the subquery is acting on a smaller set of records.
--This query returns the instance id (which can be used to construct a direct record link to FOLIO if desired), the instance hrid,
--and the contents of the transaction data note (where cataloging statistics are recorded)
--for instance records that have status "cataloged" and have a transaction data note corresponding to cataloging action
--and the instance lacks an OCLC number
--the holdings notes components can be removed if desired
select distinct ii.instance_id, ii.instance_hrid, hn.note
from instance_ext ie
join instance_identifiers ii on ii.instance_id = ie.instance_id
join holdings_notes hn on hn.instance_id = ie.instance_id
where ie.status_name = 'Cataloged'
and (hn.note_type_name LIKE 'Transaction data' and
(hn.note like '%ttype:c%' or hn.note like '%ttype:o%' or hn.note like '%ttype:f%' or hn.note like '%ttype:u%' or hn.note like '%ttype:z%'))
and ii.instance_id not in
(select instance_id from instance_identifiers where identifier_type_name = 'OCLC')
;