[Koha] SQL help please

[Barton Chittenden]

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 <
jonathan.druart at bugs.koha-community.org> 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 <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
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>