[Koha] Report help

[Nicole Engard]

Well this is awesome - thanks so much for giving me an option!

On Tue, Apr 14, 2015 at 3:39 PM, Nick Clemens <nick at 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 <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