[Koha] SQL help

[Mark Alexander]

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;