[Koha] SQL help please

[Kerrie Stevens]

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