[Koha] Report help needed

[Bernardo Gonzalez Kriegel]

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 at gmail.com


On Tue, May 20, 2014 at 1: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
>