Hi Kerrie, I'd say that the following query returns what you want: SELECT biblioitems.biblionumber, marcxml from biblioitems where ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' ) = "";
Regards, Jonathan 2016-08-09 7:36 GMT+01:00 Kerrie Stevens <[email protected]>: > Hi Everyone, > > I'm trying to get a report to identify which bib records in my Koha do not > have anything in the 942 $c Koha Item Type marc field. > > I tried to tweak a report from the report library, but it doesn't appear to > work correctly as some of the results do have things in the 942$c field. > Can anyone help me work out what I'm doing wrong? > > > SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml, LOCATE('<subfield > code="c">', > > biblioitems.marcxml, LOCATE('<datafield tag="942"', > biblioitems.marcxml)+19), > > LOCATE('</subfield>', biblioitems.marcxml, LOCATE('<subfield > code="c">', > > biblioitems.marcxml, LOCATE('<datafield tag="942"', > > biblioitems.marcxml)+19)) - LOCATE('<subfield code="c">', > biblioitems.marcxml, > > LOCATE('<datafield tag="942"', biblioitems.marcxml)+19)) AS itemtype > > FROM biblioitems, biblio > > WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL > > I'm specifically wanting a list of bib records that have nothing in the 942$c > marc field. > > Any help very much appreciated. I have almost no SQL experience/knowledge! > > Thank you > > Kerrie Stevens > > Harvest Bible College > > _______________________________________________ > Koha mailing list http://koha-community.org > [email protected] > https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
