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 <mnash...@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@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-boun...@lists.katipo.co.nz] On Behalf Of Craig > Butosi > Sent: Monday, March 18, 2019 07:29 م > To: koha@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@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@lists.katipo.co.nz > https://lists.katipo.co.nz/mailman/listinfo/koha > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha