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 < [email protected]> đã 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ũ Đỗ <[email protected]> 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 >> [email protected] >> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha >> > _______________________________________________ Koha mailing list http://koha-community.org [email protected] Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
