Paul, seems you are using two queries (";" delimits queries).
If I'm not wrong, you can useSELECT items.biblionumber,items.barcode,items.dateaccessioned, ExtractValue(biblioitems.marcxml,'//datafield[@tag="952"]/subfield[@code="x"]') AS ITEM FROM biblioitems LEFT JOIN items ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) ORDER BY items.biblionumber ASC It works on my Koha 3.2.7 (Mysql 5.1.49). Also see @code="x", instead of @code>="x". Using ">=x" will extract subfields x,y,z... HTH. Stefano > 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
