[Koha] Koha - query help needed for bilblioitems.marc

[Galen Charlton]

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 at 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