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

[Katrin Fischer]

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