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
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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
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@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@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@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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
:-P El mié., 10 ago. 2016 a las 10:56, Jonathan Druart (< jonathan.druart@bugs.koha-community.org>) escribió:
Erk indeed! Thanks Barton.
I have the habit of looking at 995 for items ;)
: 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@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@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
2016-08-10 14:32 GMT+01:00 Barton Chittenden <barton@bywatersolutions.com 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@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@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
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@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@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@lincolncolibrarydist.org
participants (5)
-
Barton Chittenden -
Jane Cothron -
Jonathan Druart -
Kerrie Stevens -
Tomas Cohen Arazi