[Koha] Koha 18.11 - SQL report to select ISBNs by Subject
Craig Butosi
cbutosi at gmail.com
Wed Mar 20 04:49:18 NZDT 2019
Hi all,
Holger, it looks your syntax works. I've modified your report to reflect a
few other things. I managed to get the following to work successfully:
SELECT * FROM
(
SELECT
biblioitems.isbn,
items.barcode,
items.itemcallnumber,
biblio.title,
biblio.author,
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>>,'%') AND isbn IS NOT NULL AND isbn
!= ''
LIMIT 20
Thanks to all of you for your assistance.
Craig Butosi, MA, MLIS, B Mus (Hons)
Library: library.craigbutosi.ca
On Tue, Mar 19, 2019 at 11:10 AM Holger Meissner <
Holger.Meissner at hs-gesundheit.de> wrote:
> 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
>
More information about the Koha
mailing list