[Koha] SQL help please

[Tomas Cohen Arazi]

:-P

El mié., 10 ago. 2016 a las 10:56, Jonathan Druart (<
jonathan.druart at bugs.koha-community.org>) escribió:

> 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
> >
> >
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
-- 
Tomás Cohen Arazi
Theke Solutions (https://theke.io <http://theke.io/>)
✆ +54 9351 3513384
GPG: B2F3C15F