Hi everyone,
Mohammad, Barton, thanks very much for this. Unfortunately, I've already
tried this report (I found it on the Koha SQL reports page online) and it
returns the following error (even after I replace the "biblioitems.marcxml"
statement with "biblio_metadata.metadata"):
*The following error was encountered:*
The database returned the following error:
Unknown column 'biblio_metadata.metadata' in 'field list'
Please check the log for further details.
which is strange. Here's what my report looks like:
SELECT *
FROM(SELECT
items.dateaccessioned,
items.barcode,
items.itemcallnumber,
biblio.title,
biblio.author,
biblioitems.publishercode,
(SELECT
ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]'))
AS Subject
FROM items
LEFT JOIN biblioitems ON
(items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and
(yyyy-mm-dd)|date>>) AS t
WHERE Subject LIKE concat('%',<<Subject>>,'%')
ORDER BY dateaccessioned DESC
No errors in /var/log/mysql either.
Any ideas?
Many thanks,
Craig Butosi, MA, MLIS, B Mus (Hons)
Library: library.craigbutosi.ca
On Tue, Mar 19, 2019 at 8:42 AM Barton Chittenden <
bar...@bywatersolutions.com> wrote:
> The data formerly found in biblioitems.xml moved to
> biblio_metadata.metadata in Koha 17.05.
>
> On Tue, Mar 19, 2019, 3:10 AM Mohammad Nashbat <mnash...@alfaisal.edu>
> wrote:
>
>> Sorry, below is the correct one.
>>
>> SELECT *
>> FROM(SELECT
>> items.dateaccessioned,
>> items.barcode,
>> items.itemcallnumber,
>> biblio.title,
>> biblioitems.isbn,
>> biblioitems.publishercode,
>> (SELECT
>> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
>> AS Subject
>> FROM items
>> LEFT JOIN biblioitems ON
>> (items.biblioitemnumber=biblioitems.biblioitemnumber)
>> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
>> WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND
>> <<and (yyyy-mm-dd)|date>>) AS t
>> WHERE Subject LIKE concat('%',<<Subject>>,'%')
>> ORDER BY dateaccessioned DESC
>>
>>
>> Thanks & Best Regards,
>>
>> Mohammed Nashbat
>> Library Instructor
>>
>>
>> -----Original Message-----
>> From: Mohammad Nashbat
>> Sent: Tuesday, March 19, 2019 10:04 ص
>> To: 'Craig Butosi'; koha@lists.katipo.co.nz
>> Subject: RE: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject
>>
>> Hi Craig,
>>
>> We are using the below SQL report, I hope it do the needful for you.
>>
>> SELECT *
>> FROM(SELECT
>> items.dateaccessioned,
>> items.barcode,
>> items.itemcallnumber,
>> biblio.title,
>> biblioitems.isbn,
>> biblioitems.publishercode,
>> (SELECT
>> ExtractValue(biblioitems.marcxml,'//datafield[@tag="650"]/subfield[@code>="a"]'))
>> AS Subject
>> FROM items
>> LEFT JOIN biblioitems ON
>> (items.biblioitemnumber=biblioitems.biblioitemnumber)
>> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
>> WHERE items.dateaccessioned BETWEEN '2019-02-01' AND '2019-03-19') AS t
>> WHERE Subject LIKE concat('%','Medicine','%') ORDER BY dateaccessioned DESC
>>
>>
>> Thanks & Best Regards,
>>
>> Mohammed Nashbat
>> Library Instructor
>> Alfaisal University
>>
>>
>> -----Original Message-----
>> From: Koha [mailto:koha-boun...@lists.katipo.co.nz] On Behalf Of Craig
>> Butosi
>> Sent: Monday, March 18, 2019 07:29 م
>> To: koha@lists.katipo.co.nz
>> Subject: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject
>>
>> Hi everyone,
>>
>> Running Koha 18.11 on Ubuntu 16.04 w/ MariaDB. Having a bit of trouble
>> understanding the db structural changes made in 18.xx and how SQL reports
>> are built in Koha 18.11. I know some tables and such have changed since the
>> old 16.xx days.
>>
>> *I'd like to create a report that draws a list of ISBNs, along with call
>> number and title, for my coverflow carousel based on subject (i.e., 650$a)
>> keyword*. I'd like to start featuring subject-based resources on the
>> carousel, not just new items.
>>
>> Any help would be very much appreciated. Just a note: there are a few
>> reports on the Koha SQL reports wiki that involve querying on the 650
>> field; but these do not work for what I would like to achieve.
>>
>> Many thanks,
>>
>> Craig Butosi, MA, MLIS, B Mus (Hons)
>> Library: library.craigbutosi.ca
>> _______________________________________________
>> Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>> ________________________________
>>
>> DISCLAIMER: This electronic mail transmission contains confidential
>> information intended only for the person(s) named. Any use, distribution,
>> copying or disclosure by any other person is strictly prohibited. If you
>> received this transmission in error, please notify the sender by reply
>> e-mail and then destroy the message. Opinions, conclusions, and other
>> information in this message that do not relate to the official business of
>> Alfaisal University shall understand to be neither given nor endorsed by
>> Alfaisal University. The contents of any attachment to this e-mail may
>> contain software viruses, which could damage your own computer system.
>> While “Alfaisal University” has taken every reasonable precaution to
>> minimize this risk, we cannot accept liability for any damage which you
>> sustain as a result of software viruses. You should carry out your own
>> virus checks before opening the attachment.
>> _______________________________________________
>> Koha mailing list http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
_______________________________________________
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha
