Do you know which version of mysql you are using? ExtractValue requires a minimum of mysql 5.1.5 to be used. I wonder if this is what is causing the error? Chris -- Chris Hobbs Director, Technology New Haven Unified School District 2011/7/19 Kathy Rippel <kdr@ckls.org>

I am trying to do a report of all items in a certain location that is NOT suppressed.

I went to the SQL report library and found the report by Chris Hobbs called *Bibs Suppressed in OPAC*. I figure if I can get that to work I can probably tweak it for the rest.However, I can't get this report to run, as is. I've tried some minor changes, but I keep getting an error message that says

*The following error was encountered: *The database returned the following error: 1 Please check the log for further details.

I don't have access to the log, as far as I know.

Here is the report, please let me know if there is something wrong with it. We are now using Community Koha.

SELECT concat( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '">', biblio.title, '</a>' ) AS title, biblio.author FROM biblioitems JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber ) WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="n"]' ) IN ('Y', '1')

Kathy

Kathleen D. Rippel Dept. Head/Consultant--Resource Access and Sharing Central Kansas Library System 1409 Williams Great Bend, KS 67530

(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax

kdr@ckls.org ******************************************************************************************** I'm currently reading: The game of kings, by Dorothy Dunnett. I'm currently listening to: The astonishing life of Octavian Nothing. Vol. 2, The kingdom on the waves, by M. T. Anderson.

_______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha

Hi Kathy - I just checked and this report should run perfectly... Actually I just tested it on your system. When you see an error like that - usually that means there was a space in-front of the 'SELECT'. That will throw that error. I've added it to your system it's report id #1085. -Brendan On Jul 19, 2011, at 2:04 PM, Kathy Rippel wrote:

I am trying to do a report of all items in a certain location that is NOT suppressed.

I went to the SQL report library and found the report by Chris Hobbs called Bibs Suppressed in OPAC. I figure if I can get that to work I can probably tweak it for the rest.

However, I can't get this report to run, as is. I've tried some minor changes, but I keep getting an error message that says

The following error was encountered: The database returned the following error: 1 Please check the log for further details.

I don't have access to the log, as far as I know.

Here is the report, please let me know if there is something wrong with it. We are now using Community Koha.

SELECT concat( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblio.biblionumber, '">', biblio.title, '</a>' ) AS title, biblio.author FROM biblioitems JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber ) WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="n"]' ) IN ('Y', '1')

Kathy

Kathleen D. Rippel Dept. Head/Consultant--Resource Access and Sharing Central Kansas Library System 1409 Williams Great Bend, KS 67530

(620-792-4865) phone (800-362-2642) toll-free, KS (620-792-5495) fax

kdr@ckls.org ******************************************************************************************** I'm currently reading: The game of kings, by Dorothy Dunnett. I'm currently listening to: The astonishing life of Octavian Nothing. Vol. 2, The kingdom on the waves, by M. T. Anderson. _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz http://lists.katipo.co.nz/mailman/listinfo/koha