Erk indeed! Thanks Barton. I have the habit of looking at 995 for items ;)
2016-08-10 14:32 GMT+01:00 Barton Chittenden <[email protected]>: > 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
