[Koha] Help needed with SQL report

[Elaine Bradtke]

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
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 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 (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
>