[Koha] UPDATE: SQL edit for Non-circulating items report

[David Sale]

ATTN: Please remove earlier message from listserv.  Further inquiry has shown that the system permissions for our local installation of Koha do not allow this user to SELECT from the biblioitems table.

David Sale
Coastline Library System
Coos/Curry County, Oregon



From: David Sale
Sent: Friday, July 31, 2020 12:12 PM
To: koha at lists.katipo.co.nz
Subject: 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