Well this is awesome - thanks so much for giving me an option!
On Tue, Apr 14, 2015 at 3:39 PM, Nick Clemens <n...@quecheelibrary.org> wrote: > Yes to both. It's not a perfect workaround by far, but thought it was worth > mentioning > > You could something like CONCAT_WS(' BR > ',IFNULL(ExtractValue(m.marcxml,'//datafield[@tag="650"][position()=1]/subfield[@code="a"]',''),IFNULLExtractValue(m.marcxml,'//datafield[@tag="650"][position()=2]/subfield[@code="a"]'),'')) > to keep out extra BRs > > I don't know of a way you can easily get around guessing on the number, that > seems to require creating a function like in the stack overflow link. If > someone does know how I would to know too ;-) > > On Tue, Apr 14, 2015 at 4:09 PM, Nicole Engard <neng...@gmail.com> wrote: >> >> 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 <n...@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 <n...@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 <neng...@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@lists.katipo.co.nz >> >>> http://lists.katipo.co.nz/mailman/listinfo/koha >> >>> >> >> >> >> >> >> >> >> -- >> >> Nick Clemens >> >> Quechee & Wilder Libraries >> >> n...@quecheelibrary.org >> >> http://www.QuecheeLibrary.org >> >> Q (802) 295-1232 W (802) 295-6341 >> >> >> > >> > >> > >> > -- >> > Nick Clemens >> > Quechee & Wilder Libraries >> > n...@quecheelibrary.org >> > http://www.QuecheeLibrary.org >> > Q (802) 295-1232 W (802) 295-6341 >> > _______________________________________________ >> > Koha mailing list http://koha-community.org >> > Koha@lists.katipo.co.nz >> > http://lists.katipo.co.nz/mailman/listinfo/koha > > > > > -- > Nick Clemens > Quechee & Wilder Libraries > n...@quecheelibrary.org > http://www.QuecheeLibrary.org > Q (802) 295-1232 W (802) 295-6341 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha