[Koha] Report help

[Katharina Penner]

We use 17.05, and so I have tried to substitute "marcxml" with
"biblio_metadata", like recommended at the Wiki page:
https://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC. But I
keep getting the following error message:

The following error was encountered:
The database returned the following error:
Unknown column 'biblio_metadata' in 'field list'
Please check the log for further details.
Return to previous page

So somehow probably more needs to happen than just substitution of words.
The 700's subfields are fine, thank you.

Katharina

On Wed, Feb 21, 2018 at 3:08 PM, Lee Jamison <ldjamison at maryu.marywood.edu>
wrote:

> Hello Katharina, I believe something like this would work. Please note
> this should work on versions up to 16.11. It appears that starting in 17.05
> the marcxml attribute was removed from the biblioitems table, so the
> accurate query would depend on your Koha version if you'd care to specify
> what that would be. I am not familiar with the associated descriptions of
> the 700s subfields, so I referenced what appeared to be an old cataloging
> index to gather the identifiers.
>
> SELECT
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="a"]')
> AS Surname,
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="b"]')
> AS Firstname,
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="c"]')
> AS 'Additions',
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="f"]')
> AS Dates,
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="g"]')
> AS Expansion,
>     ExtractValue(marcxml,'//datafield[@tag="700"]/subfield[@code>="4"]')
> AS 'Relator Code'
> FROM biblioitems;
>
> On Wed, Feb 21, 2018 at 3:14 AM, Katharina Penner <
> katharina.penner at gmail.com> wrote:
>
>> Dear all,
>>
>> I need to create a report to extract all metadata on all authors. I want
>> to
>> have each aspect - surname, name (or initials), dates, expansion of
>> initials, other possible fields - appear in a separate field/column, in a
>> delimited way. We're using UniMarc so these would be fields 700 a, b, c,
>> f,
>> g, $4. So far the data come up all in the same field.
>>
>> Please help!
>> Katharina Penner
>> _______________________________________________
>> Koha mailing list  http://koha-community.org
>> Koha at lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
>
>
> --
> Lee Jamison
> Automated Library Technician
> Marywood University
> 570-961-4586 <(570)%20961-4586>
> ldjamison at marywood.edu
>