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
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@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.
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-bounces@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.
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@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-bounces@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
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@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@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-bounces@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
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@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@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@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-bounces@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
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@lists.katipo.co.nz> Im Auftrag von Craig Butosi Gesendet: Dienstag, 19. März 2019 15:23 An: Barton Chittenden <barton@bywatersolutions.com> Cc: koha <koha@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@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@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-bounces@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
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@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@lists.katipo.co.nz> Im Auftrag von Craig Butosi Gesendet: Dienstag, 19. März 2019 15:23 An: Barton Chittenden <barton@bywatersolutions.com> Cc: koha <koha@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@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@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-bounces@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
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@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@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@lists.katipo.co.nz> Im Auftrag von Craig Butosi Gesendet: Dienstag, 19. März 2019 15:23 An: Barton Chittenden <barton@bywatersolutions.com> Cc: koha <koha@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@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@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-bounces@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
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@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@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@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@lists.katipo.co.nz> Im Auftrag von Craig Butosi Gesendet: Dienstag, 19. März 2019 15:23 An: Barton Chittenden <barton@bywatersolutions.com> Cc: koha <koha@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@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@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-bounces@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
_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
participants (5)
-
Alvaro Cornejo -
Barton Chittenden -
Craig Butosi -
Holger Meissner -
Mohammad Nashbat