[Koha] SQL help please

[Jane Cothron]

Hi--
   I believe that Nicole Engard wrote the following report for me.  It
seems to work.

SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]'
) = ""

On Mon, Aug 8, 2016 at 11:36 PM, Kerrie Stevens <KStevens at harvest.edu.au>
wrote:

> 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
>



-- 
Jane Cothron
Cataloger
Lincoln County Library District
P.O. Box 2027
Newport, Oregon 97365
541-265-3066 (phone & fax)
jcothron at lincolncolibrarydist.org