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)
Hi Elaine, yes, copy & paste :) You paste an extra 'ExtractValue(b.marcxml,' before "monthname(datecreated) AS month" Try 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)>> Bernardo -- Bernardo Gonzalez Kriegel bgkriegel@gmail.com On Tue, May 20, 2014 at 1:57 PM, Elaine Bradtke <eb@efdss.org> 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 Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
Hi Elaine, I'm really not positive this is the issue, but I would double-check to make sure that all of your single and double quotes are standard ASCII quote marks. It looks like several of them are "special" characters, perhaps automatically converted somewhere along the way in your copy/paste (thank you "smart" programs like MS Word). Again, just a guess, and maybe those characters were correct when you executed the MySQL in the report and only got converted when entered into your email application. They just look "suspect" to me. -----Original Message----- From: Elaine Bradtke [mailto:eb@efdss.org] Sent: Tuesday, May 20, 2014 12:58 PM To: koha Subject: [Koha] Report help needed 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)
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 <eb@efdss.org> 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 Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha
participants (4)
-
Bernardo Gonzalez Kriegel -
Daniel Sanford -
Elaine Bradtke -
Steven Nickerson