[Koha] SQL help please

[Jonathan Druart]

Erk indeed! Thanks Barton.

I have the habit of looking at 995 for items ;)

2016-08-10 14:32 GMT+01:00 Barton Chittenden <barton at bywatersolutions.com>:
> 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
>
>