[Koha] SQL runs outside of staff client but not inside it

Doug Dearden dearden at sarsf.org
Thu Mar 21 05:49:38 NZDT 2013

Thanks Robert, that did it.  A much more elegant solution than mine also.  I checked the wiki before I started this but didn't find that report.

Best - Doug

-----Original Message-----
From: koha-bounces at lists.katipo.co.nz [mailto:koha-bounces at lists.katipo.co.nz] On Behalf Of Robert Williams
Sent: Wednesday, March 20, 2013 10:19 AM
To: koha at lists.katipo.co.nz
Subject: Re: [Koha] SQL runs outside of staff client but not inside it


You wrote:
>I am working on some SQL to get a report for last month's acquisitions, and worked up the following code to do it:


A couple of the SQL Reports posted on the wiki have a WHERE clause that purportedly limits to the previous month. Would something like the following work?

EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="a"]') as "Title:",
EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="b"]') as subtitle,
EXTRACTVALUE(marcxml,'//datafield[@tag="245"]/subfield[@code="c"]') as "statement of responsibility",
EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="b"]') as Publisher,
EXTRACTVALUE(marcxml,'//datafield[@tag="260"]/subfield[@code="c"]') as Date,
EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="a"]') as "Call Number first part",
EXTRACTVALUE(marcxml,'//datafield[@tag="092"]/subfield[@code="b"]') as "Call Number second part", items.dateaccessioned as "Date Accessioned"
FROM biblioitems LEFT JOIN items on (biblioitems.biblioitemnumber=items.biblioitemnumber)
WHERE items.dateaccessioned >= concat(date_format(LAST_DAY(now() - interval 1 month),'%Y-%m-'),'01') 
AND items.dateaccessioned <= LAST_DAY(now() - interval 1 month) 
ORDER BY items.dateaccessioned asc;


Robert L. Williams
Open Source Services Manager
Amigos Library Services, Inc.
14400 Midway Road
Dallas, TX 75244-3509
800-843-8482, x2870
972-340-2870 (direct)
972-991-6061 (fax)

Koha mailing list  http://koha-community.org
Koha at lists.katipo.co.nz

More information about the Koha mailing list