[Koha] Help needed with SQL report

Tue Oct 10 05:57:23 NZDT 2017

Sorry,  I badly pasted the query.
Try:
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=biblio.biblionumber) LEFT JOIN  biblio_metadata m
ON (biblio.biblionumber=m.biblionumber)  ORDER BY biblio.copyrightdate asc;

On Mon, 9 Oct 2017 at 13:10 Elaine Bradtke <eb at efdss.org> wrote:

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