Thanks Heather, and double thanks for the hint about commas. It only takes one little missing piece of the puzzle for the whole thing to fail. I could really use something like SQL queries for Dummies. Elaine
On Mon, Nov 17, 2014 at 11:47 PM, Heather Braum (NEKLS) <[email protected]> wrote: > 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 > [email protected] > > "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 <[email protected]> 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 >> [email protected] >> http://lists.katipo.co.nz/mailman/listinfo/koha >> > > -- 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 [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
