I've been going through our reports, and updating them. I've fixed all the old marcxml issues. But I have a new error message with a report that used to work: "Unknown column 'place' in 'field list'" Here's the report - what field name do I need to replace place?: select b.biblionumber, author, title, ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, copyrightdate, place, publishercode, isbn from items as a left join biblio_metadata as b on (a.biblioitemnumber = b.biblioitemnumber) left join biblio as c on (b.biblionumber = c.biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc Elaine Bradtke 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)
Hi Elaine, Looks like you are looking for this query: SELECT b.biblionumber, b.author, b.title, ExtractValue(bm.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(bm.metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, bi.place, b.copyrightdate, bi.publishercode, bi.isbn FROM biblio AS b LEFT JOIN biblioitems AS bi ON b.biblionumber = bi.biblionumber LEFT JOIN biblio_metadata AS bm ON b.biblionumber = bm.biblionumber LEFT JOIN items AS i ON b.biblionumber = i.biblionumber WHERE itype = 'BK' AND holdingbranch = 'VWML' ORDER BY author ASC; You removed the join on biblioitems. Cheers, Jonathan On Tue, 9 Jan 2018 at 16:06 Elaine Bradtke <eb@efdss.org> wrote:
I've been going through our reports, and updating them. I've fixed all the old marcxml issues. But I have a new error message with a report that used to work: "Unknown column 'place' in 'field list'" Here's the report - what field name do I need to replace place?: select b.biblionumber, author, title, ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, copyrightdate, place, publishercode, isbn from items as a left join biblio_metadata as b on (a.biblioitemnumber = b.biblioitemnumber) left join biblio as c on (b.biblionumber = c.biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc
Elaine Bradtke 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) _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
Excerpts from Elaine Bradtke's message of 2018-01-09 11:06:16 -0800:
"Unknown column 'place' in 'field list'" Here's the report - what field name do I need to replace place?: select b.biblionumber, author, title, ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, copyrightdate, place, publishercode, isbn from items as a left join biblio_metadata as b on (a.biblioitemnumber = b.biblioitemnumber) left join biblio as c on (b.biblionumber = c.biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc
I took a look at the 17.05 schema here: http://schema.koha-community.org/17_05/index.html. It seems that some things have been moved to biblioitems, though I'm not familiar with the history. I was able to get your query to work (or at least, not give errors) by rewriting it as follows: select biblionumber, biblio.author, biblio.title, ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(biblio_metadata.metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, biblio.copyrightdate, biblioitems.place, biblioitems.publishercode, biblioitems.isbn from items left join biblioitems using (biblionumber) left join biblio_metadata using (biblionumber) left join biblio using (biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc;
Excellent, that works! Thank you Elaine Bradtke 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 Tue, Jan 9, 2018 at 11:40 AM, Mark Alexander <marka@pobox.com> wrote:
Excerpts from Elaine Bradtke's message of 2018-01-09 11:06:16 -0800:
"Unknown column 'place' in 'field list'" Here's the report - what field name do I need to replace place?: select b.biblionumber, author, title, ExtractValue(metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, copyrightdate, place, publishercode, isbn from items as a left join biblio_metadata as b on (a.biblioitemnumber = b.biblioitemnumber) left join biblio as c on (b.biblionumber = c.biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc
I took a look at the 17.05 schema here: http://schema.koha-community. org/17_05/index.html. It seems that some things have been moved to biblioitems, though I'm not familiar with the history.
I was able to get your query to work (or at least, not give errors) by rewriting it as follows:
select biblionumber, biblio.author, biblio.title, ExtractValue(biblio_metadata.metadata, '//datafield[@tag="245"]/subfield[@code="b"]') as subtitle, ExtractValue(biblio_metadata.metadata, '//datafield[@tag="250"]/subfield[@code="a"]') as edition, biblio.copyrightdate, biblioitems.place, biblioitems.publishercode, biblioitems.isbn from items left join biblioitems using (biblionumber) left join biblio_metadata using (biblionumber) left join biblio using (biblionumber) where itype = 'BK' and holdingbranch = 'VWML' order by author asc; _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (3)
-
Elaine Bradtke -
Jonathan Druart -
Mark Alexander