Hi all, I wrote this report to get all subject headings in a Koha system that start with key phrases: SELECT lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS lcsh FROM biblioitems) AS subjects WHERE lcsh LIKE "College%" OR lcsh LIKE "Seminary%" GROUP BY lcsh What's happening is if there is more than one 610 on a bib record it's showing them both on one line. I tried using DISTINCT, but that's didn't do the trick, so I'm up for ideas. Nicole
Hi Nicole, On Fri, Jul 1, 2011 at 9:33 AM, Nicole Engard <nengard@gmail.com> wrote:
Hi all,
I wrote this report to get all subject headings in a Koha system that start with key phrases:
SELECT lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS lcsh FROM biblioitems) AS subjects WHERE lcsh LIKE "College%" OR lcsh LIKE "Seminary%" GROUP BY lcsh
What's happening is if there is more than one 610 on a bib record it's showing them both on one line. I tried using DISTINCT, but that's didn't do the trick, so I'm up for ideas.
I did some work with ExtractValue and marcxml a while back and IIRC this has to do with the way ExtractValue returns the data from the xml. I was unable to find a workaround at the time, but perhaps someone else has found one. Kind Regards, Chris
Unfortunately, that's just the way that ExtractValue works. From the manual: If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string. Since MARC doesn't give us any means of differentiating between the multiple fields aside from their order, there isn't much that can be done here. And if the desired 610 field isn't the first in the series of them, the LIKE clause won't match; you'd need % in front, as well. MARC for the fail.. :( -Ian 2011/7/1 Chris Nighswonger <cnighswonger@foundations.edu>
Hi Nicole,
On Fri, Jul 1, 2011 at 9:33 AM, Nicole Engard <nengard@gmail.com> wrote:
Hi all,
I wrote this report to get all subject headings in a Koha system that start with key phrases:
SELECT lcsh FROM (SELECT biblionumber, ExtractValue(marcxml,'//datafield[@tag="610"]/subfield[@code>="a"]') AS lcsh FROM biblioitems) AS subjects WHERE lcsh LIKE "College%" OR lcsh LIKE "Seminary%" GROUP BY lcsh
What's happening is if there is more than one 610 on a bib record it's showing them both on one line. I tried using DISTINCT, but that's didn't do the trick, so I'm up for ideas.
I did some work with ExtractValue and marcxml a while back and IIRC this has to do with the way ExtractValue returns the data from the xml. I was unable to find a workaround at the time, but perhaps someone else has found one.
Kind Regards, Chris
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
-- Ian Walls Lead Development Specialist ByWater Solutions ALA Booth 732 Phone # (888) 900-8944 http://bywatersolutions.com ian.walls@bywatersolutions.com Twitter: @sekjal
On Fri, Jul 1, 2011 at 9:52 AM, Ian Walls <ian.walls@bywatersolutions.com>wrote:
Unfortunately, that's just the way that ExtractValue works. From the manual:
If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string.
The sad thing is if it was comma or otherwise delimited we could do some splitting foo. But spaces will inevitably appear in the date in the specified fields. Kind Regards, Chris
On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger < cnighswonger@foundations.edu> wrote:
On Fri, Jul 1, 2011 at 9:52 AM, Ian Walls <ian.walls@bywatersolutions.com
wrote:
Unfortunately, that's just the way that ExtractValue works. From the manual:
If multiple matches are found, the content of the first child text node of each matching element is returned (in the order matched) as a single, space-delimited string.
The sad thing is if it was comma or otherwise delimited we could do some splitting foo. But spaces will inevitably appear in the date in the specified fields.
Read 'data' for 'date'....
On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:
The sad thing is if it was comma or otherwise delimited we could do some splitting foo. But spaces will inevitably appear in the date in the specified fields.
Chris, in this library's case (cause they do have professional catalogers) the subject headings all end in periods ... could we use that instead of the comma/space? Nicole
On Fri, Jul 1, 2011 at 10:19 AM, Nicole Engard <nengard@gmail.com> wrote:
On Fri, Jul 1, 2011 at 10:09 AM, Chris Nighswonger <cnighswonger@foundations.edu> wrote:
The sad thing is if it was comma or otherwise delimited we could do some splitting foo. But spaces will inevitably appear in the date in the specified fields.
Chris, in this library's case (cause they do have professional catalogers) the subject headings all end in periods ... could we use that instead of the comma/space?
Assuming there were no periods embedded anywhere else in the data its possible. The 'foo' I mentioned amounts to writing a stored procedure in your db do handle the splitting since MySQL does not have a splitting function. So it would require some coding apart from the reports tool in Koha. Kind Regards, Chris
I wrote this report to get all subject headings in a Koha system that start with key phrases:
You can get your headings with a Perl script. You can also export all your biblio records in ISO2709 and then combine various standard Linux commands to extract and select your headings. For example, on Linux Shell: mysql my_koha_db_name -e 'SELECT marc FROM biblioitems' | \ yaz-marcdump | grep ^610 | sort | uniq After grep ^610, you can add a text filter to extract just $a without field name and other subfields. -- Frédéric DEMIANS http://www.tamil.fr/u/fdemians.html
participants (4)
-
Chris Nighswonger -
Frédéric Demians -
Ian Walls -
Nicole Engard