[Koha] Help needed with SQL report
Elaine Bradtke
eb at efdss.org
Tue Oct 10 06:07:47 NZDT 2017
OK, that works. Now I just need to limit it to the range of biblio
numbers. I think I can manage that.
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 Mon, Oct 9, 2017 at 9:42 AM, Jonathan Druart <
jonathan.druart at bugs.koha-community.org> wrote:
> 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
>>>>
>>>
>>
More information about the Koha
mailing list