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'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 Thu, Oct 5, 2017 at 8:11 AM, Jonathan Druart < jonathan.dru...@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 <e...@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.meiss...@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-boun...@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 >> > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha