[Koha] Help needed with SQL report

[Jonathan Druart]

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 at 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 at 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 at 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's Park Road, London NW1 7AY
> > 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 at lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> _______________________________________________
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>