OK, that works. Now I just need to limit it to the range of biblio numbers. I think I can manage that. 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) On Mon, Oct 9, 2017 at 9:42 AM, Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
Sorry, I badly pasted the query. Try: SELECT biblioitems.biblionumber,biblio.title,biblio.copyrightdate, ExtractValue(m.metadata, '//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) LEFT JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) ORDER BY biblio.copyrightdate asc;
On Mon, 9 Oct 2017 at 13:10 Elaine Bradtke <eb@efdss.org> wrote:
this time it says: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) ORDER BY biblio' at line 7 I think we're getting closer.
Elaine Bradtke Data Wrangler VWML English Folk Dance and Song Society | http://www.efdss.org Cecil Sharp House, 2 Regent <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> 's Park Road, London NW1 7AY <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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)
On Thu, Oct 5, 2017 at 8:11 AM, Jonathan Druart < jonathan.druart@bugs.koha-community.org> wrote:
Since 17196 (17.05) you need to join on biblio_metadata Try the following query: SELECT biblioitems.biblionumber, biblio.title, biblio.copyrightdate, ExtractValue(m.metadata, '//datafield[@tag="260"]/subfield[@code="c"]') AS text FROM items LEFT JOIN biblioitems on (items.biblioitemnumber= biblioitems.biblioitemnumber) LEFT JOIN biblio on (biblioitems.biblionumber= LEFT JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) ORDER BY biblio.copyrightdate asc;
On Thu, 5 Oct 2017 at 11:43 Elaine Bradtke <eb@efdss.org> wrote:
It gives the error message: *The following error was encountered:* The database returned the following error: Unknown column 'biblioitems.marcxml' in 'field list'
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 <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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)
On Thu, Oct 5, 2017 at 3:16 PM, Holger Meissner < Holger.Meissner@hs-gesundheit.de> wrote:
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@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 <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> 's Park Road, London NW1 7AY <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g> Tel +44 (0) 20 7485 2206 <+44%2020%207485%202206> (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 https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha