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ũ Đỗ <quyn...@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 > _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha