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

[Barton Chittenden]

Craig,

You need to join biblio_metadata in the FROM clause (I also removed the
outer SELECT statement, It's going to slow down your query, and it doesn't
do much).

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 bibio_metadata on (items.biblionumber =
bibio_metadata.biblionumber)
WHERE items.dateaccessioned BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and
(yyyy-mm-dd)|date>>) AS t
AND ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
 LIKE concat('%',<<Subject>>,'%')
ORDER BY dateaccessioned DESC


On Tue, Mar 19, 2019 at 10:23 AM Craig Butosi <cbutosi at gmail.com> wrote:

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