[Koha] Report help

[Nick Clemens]

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