Does indeed make sense, Caroline. Thank you! Report now generates much more sensible results, though it's interesting that the previous version was also finding things. If the SUBSTRING was off by 1, in theory almost all of the values should have been spaces since the 008/32 is almost always blank (for books, at least). But I think we're on the right track now. Many thanks again. Cab Vinton, Director Plaistow Public Library On Tue, Aug 24, 2021 at 1:20 PM Caroline Cyr-La-Rose <caroline.cyr-la-rose@inlibro.com> wrote:
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