Elaine, try the below. It looks like you were missing some commas -- that was what was causing the syntax errors; you can then name the columns like I did below or however you want using as ___ single-word (as 780t) or as "two or more words" (as 780w field_).
Make sure every column of data in the select statement has a comma after it, except for the last on before the from statement. Hope that helps! -- it ran on my system. SELECT biblio.biblionumber,biblio.title,biblio.notes,biblioitems.publishercode,biblioitems.itemtype, ExtractValue(biblioitems.marcxml, '//datafield[@tag="780"]/subfield[@code="t"]') as 780t, ExtractValue(biblioitems.marcxml, '//datafield[@tag="780"]/subfield[@code="w"]') as "780w field", ExtractValue(biblioitems.marcxml, '//datafield[@tag="866"]/subfield[@code="a"]') as 866a, ExtractValue(biblioitems.marcxml, '//datafield[@tag="866"]/subfield[@code="z"]') as 866z FROM biblioitems LEFT JOIN biblio USING (biblionumber) WHERE biblioitems.itemtype='CR' Heather Braum NExpress Coordinator Resource Sharing Librarian Northeast Kansas Library System hbr...@nekls.org "The illiterate of the 21st century will not be those who cannot read and write, but those who cannot learn, unlearn, and relearn." ~Alvin Toffler, *Rethinking the Future* On Mon, Nov 17, 2014 at 5:38 PM, Elaine Bradtke <e...@efdss.org> wrote: > 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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
