[Koha] SQL report to search for series statements and volume numbers

[Michael Kuhn]

Hi Katrin

You wrote:

 > if you use
 >
 > ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]') =
 > <<Series statement>>
 >
 > and there are multiple 490$a, they will appear as one space separated
 > string. That means, that you need to use LIKE and truncation with % to
 > locate the ones with the entry you are looking for.

So rewriting my statement I tried the following:

SELECT biblionumber,
   ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]'),
   ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="v"]')
FROM biblio_metadata
WHERE 
ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]') 
LIKE CONCAT ('%', <<Series statement>>, '%')
ORDER BY 
ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="v"]')

Now it will find all records - but as expected the sorting will not be 
correct: If there is more than one series statement in the record it 
will show all volume numbers of the different MARC 490 entries in one 
line - but the correct number is not necessarily the first one.

 > If you want to target the first, second, etc., you can use:
 >
 > ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"][1]')

Yes, but it doesn't really help in this case (at least not with my 
simple kind of statement) because the searched for series is not always 
in the same position in the MARC 490 entries in all records... Luckily 
Roger Grossmann came up with a great solution to circumvent this problem 
(see the next posting in this thread).

Thanks anyway for the hint about the LIKE clause - here I learned how to 
use it together with the << >> syntax.

Best wishes: Michael
--
Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch



> On 08.04.21 19:23, Michael Kuhn wrote:
>> Hi
>>
>> Our library is using Debian GNU/Linux 10 and Koha 20.11.
>>
>> We are trying to extract the series statement (MARC 490$a) and volume
>> numbers (MARC 490$v) from the MARCXML data. Some title data records
>> more than one MARC 490 entry. We came up with the following SQL
>> statement:
>>
>> SELECT biblionumber,
>> ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]'),
>> ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="v"]')
>> FROM biblio_metadata
>> WHERE
>> ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"]') =
>> <<Series statement>>
>> ORDER BY
>> ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="v"]')
>>
>> This will only find and sort the records that have just one and only
>> one MARC 490 entry. Even if the given series statement is in the first
>> MARC 490 entry the record will not show up if the same record has a
>> second MARC 490 entry.
>>
>> We then changed the SQL statement to:
>>
>> SELECT biblionumber,
>> ExtractValue(metadata,'//datafield[@tag="490"][1]/subfield[@code="a"]'),
>> ExtractValue(metadata,'//datafield[@tag="490"][1]/subfield[@code="v"]')
>> FROM biblio_metadata
>> WHERE
>> ExtractValue(metadata,'//datafield[@tag="490"][1]/subfield[@code="a"]')
>> = <<Series statement>>
>> ORDER BY
>> ExtractValue(metadata,'//datafield[@tag="490"][1]/subfield[@code="v"]')
>>
>> At least this will find all records that have the given series
>> statement in the first MARC 490 entry. But it will (of course) not
>> show or sort the records that have the given series statement in the
>> second, third, etc MARC 490 entry.
>>
>> According to
>> https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC I
>> understand that ExtractValue concatenates multiple tags into a single
>> field but unfortunately I couldn't come up with a solution to our
>> problem...
>>
>> Using column "seriestitle" from table "biblio" doesn't help because it
>> behaves the same and there is no possibility to search for the volume
>> number of the series (MARC 490$v).
>>
>> Can anyone please help us out?
>>
>> Best wishes: Michael Kuhn
> _______________________________________________
> 
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha