[Koha] Koha 18.11 - SQL report to select ISBNs by Subject

[Craig Butosi]

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 <
barton at 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 <MNashbat at 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 at 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-bounces at lists.katipo.co.nz] On Behalf Of Craig
>> Butosi
>> Sent: Monday, March 18, 2019 07:29 م
>> To: koha at 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 at 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 at lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>