[Koha] Report help

[Nick Clemens]

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"]',''),IFNULL
ExtractValue(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 <nengard at 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 <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
>



-- 
Nick Clemens
Quechee & Wilder Libraries
Nick at quecheelibrary.org
http://www.QuecheeLibrary.org
Q (802) 295-1232 W (802) 295-6341