[Koha] Report help

[Nicole Engard]

Don't I have to guess at what the max number of subjects would be
then? and then won't I have a bunch of BRs all over the place if there
is only one subject?

On Tue, Apr 14, 2015 at 2:02 PM, Nick Clemens <nick at quecheelibrary.org> wrote:
> You can also use the position marker in the xpath to pick an arbitrary
> number of subject headings:
>
> GROUP_CONCAT(DISTINCT
> ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]'),'
> BR
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'
> BR
> ',ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=3]/subfield[@code="a"]')
> SEPARATOR ' BR ') AS 'Subject'
>
> On Tue, Apr 14, 2015 at 1:17 PM, Nick Clemens <nick at quecheelibrary.org>
> wrote:
>
>> I think the problem is that return from ExtractValue returns only one item
>> so the GROUP_CONCAT is only seeing one thing come in and doesn't add the
>> separator:
>>
>> See this:
>>
>> http://stackoverflow.com/questions/10808149/mysql-use-extractvaluexml-value-values-to-get-all-multiple-values-split-on
>>
>> On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard at gmail.com> wrote:
>>
>>> Hi all,
>>>
>>> I'm working on this report and I want there to be a BR between each
>>> distinct 650a - but for some reason it's not working. I worked on this
>>> in channel for a while and you can see the log here:
>>> http://irc.koha-community.org/koha/2015-04-14#i_1663170
>>>
>>> Here's the report:
>>>
>>>
>>> Select b.biblionumber, GROUP_CONCAT(DISTINCT ExtractValue(m.marcxml,
>>> '//datafield[@tag="650"]/subfield[@code="a"]') SEPARATOR ' BR ') AS
>>> 'Subject',i.itype AS 'IType'
>>> FROM biblio b
>>> LEFT JOIN biblioitems m using (biblionumber)
>>> left join items i using (biblioitemnumber)
>>> WHERE i.location in
>>>
>>> ('ARCHIVES','ARCHSIZE','HISTORYMED','HISTMEDOVZ','HISTMEDREF','RAREBKROOM','RAREOVRSIZ')
>>> and i.itype != 'JOURNAL' and ExtractValue(m.marcxml,
>>> '//datafield[@tag="650"]/subfield[@code="a"]') is not null and
>>> ExtractValue(m.marcxml, '//datafield[@tag="650"]/subfield[@code="a"]')
>>> != ''
>>> group by i.biblionumber
>>> ORDER BY b.biblionumber
>>>
>>>
>>>
>>> Thanks in advance!
>>> Nicole
>>> _______________________________________________
>>> Koha mailing list  http://koha-community.org
>>> Koha at lists.katipo.co.nz
>>> http://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
>>
>>
>> --
>> Nick Clemens
>> Quechee & Wilder Libraries
>> Nick at quecheelibrary.org
>> http://www.QuecheeLibrary.org
>> Q (802) 295-1232 W (802) 295-6341
>>
>
>
>
> --
> Nick Clemens
> Quechee & Wilder Libraries
> Nick at quecheelibrary.org
> http://www.QuecheeLibrary.org
> Q (802) 295-1232 W (802) 295-6341
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha