It looks like the issue is '//datafield[@tag=“022”]/ subfield[@code=“a”]’), ExtractValue(b.marcxml, monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS
The last ExtractValue(b.marcxml, doesn't have a closing ) and you've stopped extracting XML so that is causing one error use //datafield[@tag=“022”]/ subfield[@code=“a”]’), monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS You've also got a mix of ’ and ' which are slightly different and causing MySQL to misread the sections. Likewise on the line '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml, you'll note the a and b subfield codes have different quotation marks, the one around a are right and the ones around b are not, a lot of the tags and codes are using those and MySQL often errors out due to the that. The first query you sent has all of those properly formatted. Here's a cleaned up copy SELECT ExtractValue(b.marcxml, '//datafield[@tag="100"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="b"]'), ExtractValue(b.marcxml, '//datafield[@tag="245"]/subfield[@code="c"]'), ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="t"]'), ExtractValue(b.marcxml, '//datafield[@tag="773"]/subfield[@code="g"]'), ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="b"]'), ExtractValue(b.marcxml, '//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml, '//datafield[@tag="300"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="020"]/subfield[@code="a"]'), ExtractValue(b.marcxml, '//datafield[@tag="022"]/subfield[@code="a"]'), monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS biblionumber FROM biblio LEFT JOIN biblioitems b USING (biblionumber) WHERE datecreated BETWEEN <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>> Daniel Sanford Assistant Technical Librarian for Digital Infrastructure American Philosophical Society 105 South 5th Street Philadelphia, PA 19106 Telephone: (215)-599-4313 On Tue, May 20, 2014 at 12:57 PM, Elaine Bradtke <[email protected]> wrote: > I'll admit, I'm the queen of cut and paste. What I don't know about SQL > would fill a book. > > Using a report I already have on hand: > SELECT ExtractValue(b.marcxml, > '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, > '//datafield[@tag="260"]/subfield[@code="a"]'),ExtractValue(b.marcxml, > '//datafield[@tag="260"]/subfield[@code="c"]'), monthname(datecreated) AS > month, year(datecreated) AS year, biblionumber AS biblionumber > FROM biblio > LEFT JOIN biblioitems b USING (biblionumber) > WHERE datecreated BETWEEN <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>> > > I tried to add some fields as per a request from one of our staff who would > like a rather thorough list of items added to the catalogue within a time > frame. > > SELECT ExtractValue(b.marcxml, > '//datafield[@tag=“100”]/subfield[@code="a"]'), ExtractValue(b.marcxml, > '//datafield[@tag="245"]/subfield[@code="a"]'), ExtractValue(b.marcxml, > '//datafield[@tag="245"]/subfield[@code=“b”]’), ExtractValue(b.marcxml, > '//datafield[@tag="245"]/subfield[@code=“c”]’), ExtractValue(b.marcxml, > '//datafield[@tag="260"]/subfield[@code="a"]'), ExtractValue(b.marcxml, > '//datafield[@tag=“773”]/subfield[@code=“t”]’), ExtractValue(b.marcxml, > '//datafield[@tag=“773”]/subfield[@code=“g”]’), ExtractValue(b.marcxml, > '//datafield[@tag="260"]/subfield[@code=“b”]’), ExtractValue(b.marcxml, > '//datafield[@tag="260"]/subfield[@code="c"]'), ExtractValue(b.marcxml, > '//datafield[@tag=“300”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, > '//datafield[@tag=“020”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, > '//datafield[@tag=“022”]/subfield[@code=“a”]’), ExtractValue(b.marcxml, > monthname(datecreated) AS month, year(datecreated) AS year, biblionumber AS > biblionumber > FROM biblio > LEFT JOIN biblioitems b USING (biblionumber) > WHERE datecreated BETWEEN <<Between (yyyy-mm-dd)>> AND <<and (yyyy-mm-dd)>> > > > The first report works. The second doesn't. I'm sure it's a syntax error, > or something like that, but I'm blundering around in the dark. > > > Her request was for the following info: 100 field = Author | 245 = Title > proper (including subtitles and statement of responsibility) | 773(?) = > Periodical title |260 = place, publisher, and date | 300 = Extent | 020 = > ISBN / 022 = ISSN > > > Can anyone help? > > -- > 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 > _______________________________________________ Koha mailing list http://koha-community.org [email protected] http://lists.katipo.co.nz/mailman/listinfo/koha
