SQL edit for Non-circulating items report
Hello, Our library is engaged in a weeding project that requires a SQL report for Koha to be customized. The report we are attempting to customize was written by ByWater Solutions, and it generates a list of items "not seen" in X years, by branch and shelf location. The existing report is written in SQL as follows: SELECT i.itemcallnumber, b.biblionumber, b.title, b.author, b.copyrightdate, i.barcode, i.datelastseen, i.issues AS totalcheckouts, i.dateaccessioned FROM items i LEFT JOIN issues USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) WHERE homebranch = <<Home Library|branches>> AND location = <<Shelving location|LOC>> AND i.withdrawn=0 AND i.itemlost=0 AND i.damaged=0 AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND YEAR(NOW())-YEAR(i.datelastseen) > <<Years NOT seen>> ORDER BY i.itemcallnumber ASC We would like to modify this report so that in addition to returning the author, title and copyright date, it also returns the ISBN. However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author", etc.) breaks the report and renders it non-functional. Can anyone suggest how the SQL given above should be modified to create a functional report that will generate a list of items "not seen" in X years, by branch and shelf location, and that will return the ISBN *in addition to* the title, author, copyright date and barcode for each item listed in the report? Thanks, David Sale Coastline Library Network Coos/Curry Counties, Oregon
The ISBN is in the biblioitems table. Change b.isbn to bi.isbn and add LEFT JOIN biblionitems bi USING (biblionumber) before the line that starts with WHERE. On 7/31/20 2:12 PM, David Sale wrote:
Hello,
Our library is engaged in a weeding project that requires a SQL report for Koha to be customized.
The report we are attempting to customize was written by ByWater Solutions, and it generates a list of items "not seen" in X years, by branch and shelf location.
The existing report is written in SQL as follows:
SELECT i.itemcallnumber, b.biblionumber, b.title, b.author, b.copyrightdate, i.barcode, i.datelastseen, i.issues AS totalcheckouts, i.dateaccessioned FROM items i LEFT JOIN issues USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) WHERE homebranch = <<Home Library|branches>> AND location = <<Shelving location|LOC>> AND i.withdrawn=0 AND i.itemlost=0 AND i.damaged=0 AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND YEAR(NOW())-YEAR(i.datelastseen) > <<Years NOT seen>> ORDER BY i.itemcallnumber ASC
We would like to modify this report so that in addition to returning the author, title and copyright date, it also returns the ISBN.
However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author", etc.) breaks the report and renders it non-functional.
Can anyone suggest how the SQL given above should be modified to create a functional report that will generate a list of items "not seen" in X years, by branch and shelf location, and that will return the ISBN *in addition to* the title, author, copyright date and barcode for each item listed in the report?
Thanks, David Sale Coastline Library Network Coos/Curry Counties, Oregon _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
-- Tim McMahon West Liberty Public Library 319-627-2084
Excerpts from David Sale's message of 2020-07-31 19:12:13 +0000:
However, adding "b.isbn" to the SELECT list (as with "b.title", "b.author", etc.) breaks the report and renders it non-functional.
You might find this page useful, since it does provide some isbn examples: https://wiki.koha-community.org/wiki/SQL_Reports_Library The problem with your query is that isbn is not a field in the biblios table; it is in the biblioitems table. You'll need to join that table to the query, perhaps like the one below. SELECT bi.isbn, i.itemcallnumber, b.biblionumber, b.title, b.author, b.copyrightdate, i.barcode, i.datelastseen, i.issues AS totalcheckouts, i.dateaccessioned FROM items i LEFT JOIN issues USING (itemnumber) LEFT JOIN biblio b USING (biblionumber) LEFT JOIN biblioitems bi USING (biblionumber) WHERE homebranch = <<Home Library|branches>> AND location = <<Shelving location|LOC>> AND i.withdrawn=0 AND i.itemlost=0 AND i.damaged=0 AND i.itemnumber NOT IN (SELECT issues.itemnumber FROM issues) AND YEAR(NOW())-YEAR(i.datelastseen) > <<Years NOT seen>> ORDER BY i.itemcallnumber ASC
participants (3)
-
David Sale -
Mark Alexander -
Tim McMahon