[Koha] Help needed with SQL report

[Holger Meissner]

Hi Elaine,

how about this:

SELECT
    biblioitems.biblionumber,
    biblio.title,
    ExtractValue(biblioitems.marcxml, '//datafield[@tag="260"]/subfield[@code="c"]') AS text
FROM items
LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE
    items.biblionumber >= '17920' AND items.biblionumber <= '17959'
ORDER BY text ASC


-----Ursprüngliche Nachricht-----
Von: Koha [mailto:koha-bounces at lists.katipo.co.nz] Im Auftrag von Elaine Bradtke
Gesendet: Donnerstag, 5. Oktober 2017 15:08
An: koha
Betreff: [Koha] Help needed with SQL report

I need a report that gives bib number, title and 260$c from a set of records within a range of biblio numbers.

This is what the wizard gives me:
SELECT  biblioitems.biblionumber,biblio.title,biblio.copyrightdate FROM items LEFT JOIN biblioitems on
(items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio on
(biblioitems.biblionumber=biblio.biblionumber)   WHERE items.biblionumber
>= '17920' AND items.biblionumber <= '17959' ORDER BY 
>biblio.copyrightdate
asc

But it's only giving the year (presumably from the 008?)  I need the actual  text of the 260$c field.

Any help would be greatly appreciated.  Thanks!
I looked for a way I could configure the csv output from a list to give me that information, but didn't find it .
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 https://lists.katipo.co.nz/mailman/listinfo/koha