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-boun...@lists.katipo.co.nz [mailto:koha-boun...@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: >On 21 February 2012 10:17, Paul <pau...@aandc.org> 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 > 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 _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
