[Koha] Regarding SQL query for Analytical entries

[Suresh Kumar Tejomurtula]

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 at 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 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
>>
>