Jonathan's use of ExtractValue is correct, but item data isn't stored in biblioitems.marcxml -- as a matter of fact, it's not stored in marc at all... it's *always* generated on the fly.
You can query against items.itype instead. --Barton On Tue, Aug 9, 2016 at 4:18 AM, Jonathan Druart < [email protected]> wrote: > 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 > _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
