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
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-... On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@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-...
On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
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@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@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-...
On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@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
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@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@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@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-...
On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
Well this is awesome - thanks so much for giving me an option! On Tue, Apr 14, 2015 at 3:39 PM, Nick Clemens <nick@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@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@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@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-...
On Tue, Apr 14, 2015 at 11:05 AM, Nicole Engard <nengard@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
-- Nick Clemens Quechee & Wilder Libraries Nick@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 Nick@quecheelibrary.org http://www.QuecheeLibrary.org Q (802) 295-1232 W (802) 295-6341
Hi Nicole & Nick! I'm particularly interested with this report. What I did before was this: ExtractValue (marcxml,'//datafield[@tag="653"]/subfield[@code="a"][1]') as Keywords, ExtractValue (marcxml,'//datafield[@tag="653"]/subfield[@code="a"][2]') as Keywords, ExtractValue (marcxml,'//datafield[@tag="653"]/subfield[@code="a"][3]') as Keywords.... but the subjects/keywords are in different columns and I have arbitrarily put in up to 40 columns for this keywords. whatever report we arrive will be great and could be use by a greater number of Koha users. Cheers ya'll! :) -- View this message in context: http://koha.1045719.n5.nabble.com/Report-help-tp5835592p5835643.html Sent from the Koha-general mailing list archive at Nabble.com.
participants (3)
-
Nick Clemens -
Nicole Engard -
schnydszch