[Koha] Regarding SQL query for Analytical entries

[Barton Chittenden]

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 at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>