SQL help

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL help

Elaine Bradtke-3
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)
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help

Jonathan Druart
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 <[hidden email]> 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
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help

Mark Alexander
In reply to this post by Elaine Bradtke-3
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
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help

Elaine Bradtke-3
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 <[hidden email]> 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
> [hidden email]
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________
Koha mailing list  http://koha-community.org
[hidden email]
https://lists.katipo.co.nz/mailman/listinfo/koha