SQL report to search for series statements and volume numbers
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 -- 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@adminkuhn.ch · W www.adminkuhn.ch
Hi Michael, 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. If you want to target the first, second, etc., you can use: ExtractValue(metadata,'//datafield[@tag="490"]/subfield[@code="a"][1]') Hope this helps, Katrin 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
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@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@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Michael, an inefficient and quite long running SQL statement for your request might be the following: SELECT s.ser AS series, s.vol AS volume, s.biblionumber FROM ( SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][1]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][1]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' UNION ALL SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][2]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][2]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' UNION ALL SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][3]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][3]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' ) AS s WHERE s.ser = <<Series statement>> ORDER BY s.vol Typically there are not many catalog records with more than 3 series. If you expect more, you can add additional "UNION ALL" clauses with higher indexes. The performance depends on the size of the collection. The statement runs slow due to the full table scans but if it is not a big collection the speed might be ok. Best regards, Roger -- LMSCloud GmbH Roger Großmann - Geschäftsführer Konrad-Zuse-Platz 8 - D-81829 München e roger.grossmann@lmscloud.de w www.lmscloud.de
Am 08.04.2021 um 19:23 schrieb Michael Kuhn <mik@adminkuhn.ch>:
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 -- 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@adminkuhn.ch · W www.adminkuhn.ch _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi Roger This works exactly as it is needed, and even with correct sorting! Great! I'm not really familiar with the UNION clause but this seems a great way of using it. The SQL statement may be somewhat inefficient but is very effective though. In our database with around 30'000 test records it takes about 4 seconds, we will see how slow/fast it will be in the productive database with 165'000 records - but it will be worth the waiting! Thank you very much! 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@adminkuhn.ch · W www.adminkuhn.ch Am 12.04.21 um 08:50 schrieb Roger Grossmann:
Hi Michael,
an inefficient and quite long running SQL statement for your request might be the following:
SELECT s.ser AS series, s.vol AS volume, s.biblionumber FROM ( SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][1]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][1]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' UNION ALL SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][2]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][2]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' UNION ALL SELECT ExtractValue(a.metadata,'//datafield[@tag="490"][3]/subfield[@code="a"]') AS ser, ExtractValue(a.metadata,'//datafield[@tag="490"][3]/subfield[@code="v"]') AS vol, a.biblionumber FROM biblio_metadata a HAVING ser <> '' ) AS s WHERE s.ser = <<Series statement>> ORDER BY s.vol
Typically there are not many catalog records with more than 3 series. If you expect more, you can add additional "UNION ALL" clauses with higher indexes.
The performance depends on the size of the collection. The statement runs slow due to the full table scans but if it is not a big collection the speed might be ok.
Best regards, Roger
-- LMSCloud GmbH Roger Großmann - Geschäftsführer Konrad-Zuse-Platz 8 - D-81829 München e roger.grossmann@lmscloud.de w www.lmscloud.de
Am 08.04.2021 um 19:23 schrieb Michael Kuhn <mik@adminkuhn.ch>:
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 -- 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@adminkuhn.ch · W www.adminkuhn.ch _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Katrin Fischer -
Michael Kuhn -
Roger Grossmann