[Koha] Report help needed

[Daniel Sanford]

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 at 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 at lists.katipo.co.nz
> http://lists.katipo.co.nz/mailman/listinfo/koha
>