[Koha] Koha 18.11 - SQL report to select ISBNs by Subject
Alvaro Cornejo
cornejo.alvaro at gmail.com
Thu Mar 21 05:56:59 NZDT 2019
Hi Craig
Good to know.
A last thing to do, can you add it to the koha sql reports page?
Regards,
Alvaro
|-----------------------------------------------------------------------------------------------------------------|
Envíe y Reciba Datos y mensajes de Texto (SMS) hacia y desde cualquier
celular y Nextel
en el Perú, México y en mas de 180 paises. Use aplicaciones 2 vias via SMS
y GPRS online
Visitenos en www.perusms.com
Le mer. 20 mars 2019 à 11:48, Craig Butosi <cbutosi at gmail.com> a écrit :
> Hi everyone,
>
> Just a follow up for the community :)
>
> Thanks to the savvy brain trust that is the Koha list, I am now
> successfully using the following report to query ISBNs (+biblionumber,
> +title as required by the coverflow plugin) based on subject:
>
> SELECT DISTINCT
> biblioitems.isbn,biblio.title,biblio.biblionumber, c.imagenumber AS
> localcover,
>
> 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)
> LEFT JOIN biblioimages c ON (items.biblionumber=c.biblionumber)
> WHERE items.dateaccessioned BETWEEN "2000-01-01" AND CURDATE()
> AND
>
> ExtractValue(biblio_metadata.metadata,'//datafield[@tag="650"]/subfield[@code>="a"]')
> LIKE "%Art%"
> AND (isbn IS NOT NULL OR isbn !='')
>
> This report also includes any local cover that has been uploaded to a bib
> record. Could be useful in cases where libraries have not created a custom
> fallback cover for records without a cover. You'll also notice that the
> choice of subject is hard-coded into the report on the second last line. In
> this case, I chose %Art% to capture any book that has a subject with the
> word 'Art' in it. Final result: http://library.craigbutosi.ca/
>
> Again, thanks to everyone.
>
>
> Craig Butosi, MA, MLIS, B Mus (Hons)
> Library: library.craigbutosi.ca
>
>
> On Tue, Mar 19, 2019 at 11:49 AM Craig Butosi <cbutosi at gmail.com> wrote:
>
> > 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
> >>
> >
> _______________________________________________
> 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