[Koha] Help needed with SQL report

[Elaine Bradtke]

this time it says: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'JOIN biblio_metadata m ON (biblio.biblionumber=m.biblionumber) ORDER
BY biblio' at line 7
I think we're getting closer.

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 8:11 AM, Jonathan Druart <
jonathan.druart at bugs.koha-community.org> wrote:

> 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
>> <https://maps.google.com/?q=2+Regent's+Park+Road,+London+NW1+7AY&entry=gmail&source=g>
>> '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 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
>>
>