[Koha] SQL help please

[Jonathan Druart]

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 <KStevens at harvest.edu.au>:
> 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
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha