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

[Robert Williams]

Doug:

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:

[...snip...]

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?

SELECT
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

*********************************************
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)