[Koha] Report help needed

[Steven Nickerson]

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 at 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)