There is a particularity with substring and control fields in SQL.
The difference has to do with the starting character of the substring,
i.e. the 33 in the following sequence
SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),33,1)
MARC starts counting at 1, whereas SQL starts counting at 0. Or it might be the
reverse, I don't remember exactly. Anyway, the thing is that you can't use the
MARC character position (in this case 33 because you're looking for 008/33),
directly in the substring statement. You have to add 1.
So to look for 008/33 you have to use
SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),34,1)
to look for 008/15-17, like in the example in the link I sent, you have to use
SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),16,3)
Does that make sense?
Caroline
On 2021-08-24 12 h 13, Cab Vinton wrote:
Thanks so much, Caroline! Good excuse to brush up on my French.
Have used MarcEdit before, but not for this, so your instructions will
be vachement utiles :-)
Btw, we've been using the report below to identify which records have
issues w/ the 008/33. However, the ExtractValue statement is pulling
spaces rather than the actual values.
Would be grateful for any suggestions on why the SUBSTRING &
ExtractValue statements don't appear to be working well together.
All best,
Cab Vinton, Director
Plaistow Public Library
SELECT SUBSTRING(ExtractValue(metadata,'//controlfield[@tag=008]'),33,1)
AS LitForm, i.itemcallnumber, i.itype, i.ccode, CONCAT('<a
href=\"/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber,
'\">', biblionumber, '</a>' ) AS bibnumber
FROM biblio_metadata
LEFT JOIN items i USING (biblionumber)
ORDER BY i.biblionumber DESC, i.itemcallnumber
Caroline Cyr La Rose, M.S.I.
Bibliothécaire | Responsable de produit
Tél. : 1-833-465-4276, poste 221
caroline.cyr-la-r...@inlibro.com <mailto:caroline.cyr-la-r...@inlibro.com>
INLiBRO | Spécialistes en technologies documentaires | www.inLibro.com
<http://www.inLibro.com>
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
