Hi, On 08/22/2012 11:27 AM, Galen Charlton wrote:
On 08/22/2012 10:51 AM, Linda wrote:
I need to find records having a 773 field that are missing either a subfield "9". or a subfield "o".. I'm at a loss of how to accomplish this. Any input would be appreciated. Thanks in advance.
This should do the trick:
SELECT biblionumber FROM biblioitems WHERE marcxml RLIKE '<datafield tag="773"' AND ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="9" or @code = "o"]/text()') = '';
By the way, this query will return all bibs that lack *both* of those subfields. This variant returns bibs that lack one or the other: SELECT biblionumber FROM biblioitems WHERE marcxml RLIKE '<datafield tag="773"' AND ( ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="9"]/text()') = '' OR ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="o"]/text()') = '' ); This isn't perfect, though -- if a record has multiple 773 fields, and one of them has the required subfields while the others do not, it won't be included in the query results. Regards, Galen -- Galen Charlton Director of Support and Implementation Equinox Software, Inc. / The Open Source Experts email: gmc@esilibrary.com direct: +1 770-709-5581 cell: +1 404-984-4366 skype: gmcharlt web: http://www.esilibrary.com/ Supporting Koha and Evergreen: http://koha-community.org & http://evergreen-ils.org