Re: [Koha] Report help

2018-02-21 Thread Katharina Penner 
Yes, sorry, I had done this before for other reports and now forgot to
return to it. That did the trick!!

Thank you!
Katharina

On Wed, Feb 21, 2018 at 6:16 PM, Jonathan Druart <
jonathan.dru...@bugs.koha-community.org> wrote:

> Hello Katharina,
>
> Do you see the "Update SQL" button on the report list? Did you try it?
> It is supposed to update the query with the new table structure.
>
> Regards,
> Jonathan
>
> On Wed, 21 Feb 2018 at 13:24 Katharina Penner 
> wrote:
>
>> 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 <
>> ldjami...@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.pen...@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@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> <(570)%20961-4586>
>> > ldjami...@marywood.edu
>> >
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

2018-02-21 Thread Jonathan Druart 
Hello Katharina,

Do you see the "Update SQL" button on the report list? Did you try it?
It is supposed to update the query with the new table structure.

Regards,
Jonathan

On Wed, 21 Feb 2018 at 13:24 Katharina Penner 
wrote:

> 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  >
> 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.pen...@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@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>
> > ldjami...@marywood.edu
> >
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] Report help

2018-02-21 Thread 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 
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.pen...@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@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>
> ldjami...@marywood.edu
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] Report help

2018-02-21 Thread Katharina Penner 
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@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha