Thank you. Able to get the report using this : ExtractValue( marcxml, '//datafield[@tag="600"][1]/* ) as subject1 Regards T Suresh Kumar On Tue, 22 Jan, 2019, 10:41 Barton Chittenden <barton@bywatersolutions.com wrote:
Suresh,
Unfortunately, there's not a great way to do this -- the problem is that repeated tags in marcxml are extracted as a single value by ExtractValue. Here's what the Koha wiki has to say in the matter:
Note that ExtractValue concatenates multiple tags into a single field. These can be individually addressed using an array index after [@tag="856"]:
ExtractValue( metadata, '//datafield[@tag="856"][1]/subfield[@code="u"]' ) AS '856$u'
The index is 1-based, [@tag="856"][1] is the first tag, [@tag="856"][2] is the second, etc.
... unfortunately this means that you're stuck hard coding the number of tags in your query... so if you do this: ExtractValue( marcxml, '//datafield[@tag="600"][1]/* ) as subject1, ExtractValue( marcxml, '//datafield[@tag="600"][2]/* ) as subject2, ... ExtractValue( marcxml, '//datafield[@tag="600"][8]/* ) as subject8,
... and then one of your bibliographic records has nine subjects, you won't see it.
I've been thinking about writing a plugin to query for repeatable tags -- koha has some built in functions for handling marc data that should make pretty quick work of the problem. I think that once I write my first report plugin, many more will follow, but it's one of those things that's easy when you know how, but difficult to see your way to when you haven't done it before (at least I *hope* it's easy when you've done it before ;-) )
--Barton
On Mon, Jan 21, 2019 at 7:51 AM Suresh Kumar Tejomurtula < tejoskumar@gmail.com> wrote:
Hi,
we have analytical entries created for conference papers purchased in our library. We generated a report using the below query. But, unable to get the report the way I need. I wanted a separator between the subject entries that are repeated. But the separator is not getting added and all the subjects for a particular biblio record are coming without separator. We are using koha 16.05
SELECT biblioitems.biblionumber, ExtractValue(marcxml, '//datafield[@tag="088"]/subfield[@code="a"]') AS "Reportno", ExtractValue(marcxml, '//datafield[@tag="245"]/subfield[@code="a"]') AS "title", ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="a"]') AS "main_entry", ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="t"]') AS "main_entry_title", ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="o"]') AS "barcode", GROUP_CONCAT(ExtractValue(marcxml, '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR '||') AS "Subject" FROM biblioitems LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) where biblioitems.itemtype='CON' Group by ExtractValue(marcxml, '//datafield[@tag="773"]/subfield[@code="t"]')
Could you please let me know where iam going wrong.
-- Regards T. Suresh Kumar _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha