[Koha] Need report help from the collection mind!

[Chris Cormack]

On 30 March 2011 07:49, Kathy Rippel <kdr at ckls.org> wrote:
> I'm trying to build a simple weeding tool for one of my libraries and
> seem to be missing something. I am getting results where the
> Total_Circ is 2 or more!! The rest looks great, the results are just
> not narrow enough for this case.
>
> Context may be slightly different because we are on LLEK, but I
> believe the problem is with the SQL at
> WHERE items.homebranch='LX' AND 'Total_Circ'<2
>
> We want everything where the Total Circ is less than two, obviously!
>
Yes, the problem is by making it  'Total_Circ' you have made it a literal.
So it is not a column (or alias) anymore.

I would do this with a subquery

So like this

select CONCAT( '<a
href=\"/cgi-bin/koha/cataloguing/additem.pl?biblionumber=',biblio.biblionumber,
'\">',countitems.barcode,'</a>' ) AS barcode_href, itemcallnumber,
author, title, issues, renewals, total_circ FROM
(select (IFNULL(items.issues,0)+IFNULL(items.renewals,0)) AS
total_circ,itemnumber,barcode,issues,renewals,biblioitemnumber,itemcallnumber,homebranch
FROM items) AS countitems
LEFT JOIN biblioitems on
(countitems.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)
WHERE total_circ < 2 AND homebranch = 'LX'
ORDER BY itemcallnumber,author,title

Chris