Hello Alvaro Sorry for a late reply. I just found out many emails were sent to my spam box, including yours. The new sql code that you sent me works well. Thanks for your help. Best regards Quynh ================= M. Vũ Đỗ Quỳnh (Ph.D) Trường Đại học Thăng Long - Université de Thang Long - Thang Long University (Hanoi, Vietnam) Phó Hiệu Trưởng, phụ trách HTQT - Vice-recteur chargé de la coopération internationale - Vice-Rector in charge of International Cooperation Web: http://thanglong.edu.vn/ Vào Th 2, 8 thg 6, 2020 vào lúc 01:43 Alvaro Cornejo < cornejo.alvaro@gmail.com> đã viết:
Hi Quỳnh,
There were a couple of errors in your query.
Try this:
--------- SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ' ', 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ""
ORDER BY rand()
LIMIT 7 ----------
I´ve tried in my system (19.11) system and it works now.
Regards
Alvaro
|----------------------------------------------------------------------------------------| Stay safe / Cuídate/ Reste sécurisé *7* Switch off as you go / Apaga lo que no usas / Débranchez au fur et à mesure. *q *Recycle always / Recicla siempre / Recyclez toujours P Print only if absolutely necessary / Imprime solo si es necesario / Imprimez seulement si nécessaire
Le sam. 6 juin 2020 à 20:51, Quỳnh Vũ Đỗ <quynhvd@thanglong.edu.vn> a écrit :
Hello everyone, I am using a test database of our Library on Koha 19.11.06 (just auto-upgraded from 19.11.05) on Ubuntu 16.04.6 LTS. In the process of customizing the OPAC and installing the COverflow plugin as described at: https://bywatersolutions.com/education/coverflow-blog-post-update I have created the sql report by copying and paste the described content on that page:
Report SQL:
SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, ‘ ‘, 1) AS isbn, b.title
FROM items i
LEFT JOIN biblioitems m USING (biblioitemnumber)
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != ”
ORDER BY rand()
LIMIT 7
The only modification I have brought to the original code was to change the INTERVAL from 30 DAY to 180 DAY as the last operation made on that database was made on 25th February 2020. On running the report I got the following error message: "The database returned the following error: Incorrect parameters in the call to native function 'substring_index' Please check the log for further details"
So I checked the log at /var/log/koha/library/intranet-error.log which indicated:
[Sun Jun 07 08:21:27.385286 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: \r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385426 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: LIMIT ?, ?" with ParamValues: 0=0, 1=7] at /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 1001.: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385605 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: DBD::mysql::st fetchrow_arrayref failed: fetch() without execute() [for Statement "SELECT b.biblionumber, SUBSTRING_INDEX(m.isbn, \xe2\x80\x98 \xe2\x80\x98, 1) AS isbn, b.title\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385660 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: FROM items i\r: /usr/share/koha/intranet/cgi-bin/reports/ guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385745 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: LEFT JOIN biblioitems m USING (biblioitemnumber)\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385834 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.385966 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: WHERE DATE_SUB(CURDATE(),INTERVAL 180 DAY) <= i.dateaccessioned AND m.isbn IS NOT NULL AND m.isbn != \xe2\x80\x9d\r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.386024 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: ORDER BY rand()\r: /usr/share/koha/intranet/cgi-bin/reports/ guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.386072 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: \r: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl [Sun Jun 07 08:21:27.386321 2020] [cgi:error] [pid 30474] [client 127.0.0.1:39572] AH01215: [Sun Jun 7 08:21:27 2020] guided_reports.pl: LIMIT ?, ?" with ParamValues: 0=0, 1=7] at /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl line 839.: /usr/share/koha/intranet/cgi-bin/reports/guided_reports.pl, referer: http://localhost:8080/cgi-bin/koha/reports/guided_reports.pl
As I have limited knowledge in SQL coding, I am unsure of what to do (I remember to have tested that SQL code with Koha 16.05 and it worked then).
Does anyone have met that problem and possibly have a hint for resolution? Thanks ================= M. Vũ Đỗ Quỳnh (Ph.D) Trường Đại học Thăng Long - Université de Thang Long - Thang Long University (Hanoi, Vietnam) Phó Hiệu Trưởng, phụ trách HTQT - Vice-recteur chargé de la coopération internationale - Vice-Rector in charge of International Cooperation Web: http://thanglong.edu.vn/ _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha