I have a request for a report that is beyond my paltry abilities and that of the report builder.
I have this, and it works (thanks Liz!): SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype FROM biblioitems LEFT JOIN biblio USING (biblionumber) WHERE biblioitems.itemtype='CR' But I need to add in more columns that involve the MARC XML Specifically 780 t (previous title) 780 w (previous control no.) 866 a (holdings) 866 z (note) 867 e (source) When I try to add in: Extract value ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]') I manage to break the above query and it complains of syntax errors. Does it matter where this Extract phrase comes in? How do you handle multiple subfields (780 t and w for instance) For that matter, how do you handle multiple fields? And how can I label the columns The following (and a few variations on the theme) did not work: SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="t"]') ExtractValue(marcxml, '//datafield[@tag="780"]/subfield[@code="w"]') ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="a"]') ExtractValue(marcxml, '//datafield[@tag="866"]/subfield[@code="z"]') FROM biblioitems LEFT JOIN biblio USING (biblionumber) WHERE biblioitems.itemtype='CR' As always I am very, very grateful for whatever help you can give. Thanks -- Elaine Bradtke Data Wrangler VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent's Park Road, London NW1 7AY Tel +44 (0) 20 7485 2206 (This number is for the English Folk Dance and Song Society in London, England. If you wish to phone me personally, send an e-mail first. I work off site) -------------------------------------------------------------------------- Registered Company No. 297142 Charity Registered in England and Wales No. 305999 --------------------------------------------------------------------------- "Writing about music is like dancing about architecture" --Elvis Costello (Musician magazine No. 60 (October 1983), p. 52) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha