Re: [Koha] How to include 952$x in a report
At 10:24 AM 2/21/2012 +1300, Chris Cormack wrote:
Help requested please from an expert in MySQL reports. Â The code below works perfectly, but I want to add 952$x (a text string for the condition of our holdings as in "good/fair" or "fine tpb") to the first line.
If I add items.more_subfields_xml (which includes 952$x) I get a
On 21 February 2012 10:17, Paul <paul.a@aandc.org> wrote: plethora of
formatted XML that is unsuitable for copying straight into a spreadsheet. [snip first trial code]
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
Thanks Chris, I had tried something along those lines, and have now tried again. Started with deleting the: WHERE biblionumber=14), per mysql manual "The statement selects all rows if there is no WHERE clause", but still get error "can't use undefined value as an ARRAY reference" at guided_reports.pl line 618. After multiple trial/error changes to punctuation, etc, here's what I have, still non-functional: SELECT items.biblionumber,items.barcode,items.dateaccessioned FROM items; SELECT ExtractValue(marcxml,'//datafield[@tag="952"]/subfield[@code>="x"]') AS ITEM FROM biblioitems LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.biblionumber asc Removing the "SELECT ExtractValue ..." and preceding semi-colon works fine, I'm just totally flummoxed on getting the 952$x part to function. Is another LEFT JOIN required? Could you possibly suggest the correct syntax? Many thanks. Paul
Hi Paul, Perhaps this might give you what you want? (Using 999) SELECT ExtractValue(more_subfields_xml, '/collection/record/datafield[\@tag=\"999\"]/subfield[\@code=\"x\"]'), items.barcode, items.biblionumber,items.barcode,items.dateaccessioned FROM items LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.biblionumber asc Cheers Chris Meech -----Original Message----- From: koha-bounces@lists.katipo.co.nz [mailto:koha-bounces@lists.katipo.co.nz] On Behalf Of Paul Sent: Tuesday, 21 February 2012 11:47 a.m. To: Chris Cormack; koha Subject: Re: [Koha] How to include 952$x in a report At 10:24 AM 2/21/2012 +1300, Chris Cormack wrote:
Help requested please from an expert in MySQL reports. Â The code below works perfectly, but I want to add 952$x (a text string for the condition of our holdings as in "good/fair" or "fine tpb") to the first line.
If I add items.more_subfields_xml (which includes 952$x) I get a
On 21 February 2012 10:17, Paul <paul.a@aandc.org> wrote: plethora of
formatted XML that is unsuitable for copying straight into a spreadsheet. [snip first trial code]
http://wiki.koha-community.org/wiki/SQL_Reports_Library#Query_MARC
Thanks Chris, I had tried something along those lines, and have now tried again. Started with deleting the: WHERE biblionumber=14), per mysql manual "The statement selects all rows if there is no WHERE clause", but still get error "can't use undefined value as an ARRAY reference" at guided_reports.pl line 618. After multiple trial/error changes to punctuation, etc, here's what I have, still non-functional: SELECT items.biblionumber,items.barcode,items.dateaccessioned FROM items; SELECT ExtractValue(marcxml,'//datafield[@tag="952"]/subfield[@code>="x"]') AS ITEM FROM biblioitems LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.biblionumber asc Removing the "SELECT ExtractValue ..." and preceding semi-colon works fine, I'm just totally flummoxed on getting the 952$x part to function. Is another LEFT JOIN required? Could you possibly suggest the correct syntax? Many thanks. Paul _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (2)
-
Chris Meech -
Paul