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

Wed Mar 20 04:09:51 NZDT 2019

Hi Craig,

does it work like this?

SELECT * FROM
(
    SELECT
        items.dateaccessioned,
        items.barcode,
        items.itemcallnumber,
        biblio.title,
        biblio.author,
        biblioitems.publishercode,
        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)
    LEFT JOIN biblio_metadata ON (biblio_metadata.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

Regards,
Holger

-----Ursprüngliche Nachricht-----
Von: Koha <koha-bounces at lists.katipo.co.nz> Im Auftrag von Craig Butosi
Gesendet: Dienstag, 19. März 2019 15:23
An: Barton Chittenden <barton at bywatersolutions.com>
Cc: koha <koha at lists.katipo.co.nz>
Betreff: Re: [Koha] Koha 18.11 - SQL report to select ISBNs by Subject

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
>>
>
_______________________________________________
Koha mailing list  http://koha-community.org Koha at lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha