[Koha] A more complete report on lost documents

[Michael Kuhn]

Hi Vanda Koha

You wrote:

 > *The following error was encountered:* The database returned the
 > following error:
 > You have an error in your SQL syntax; check the manual that
 > corresponds to your MariaDB server version for the right syntax to use
 > near 'FROM items i LEFT JOIN biblio b ON 
(i.biblionumber=b.biblionumber) LEFT JOIN b' at line 6
 > Please check the log for further details.

I omitted some errors and then tested the following SQL statement using 
the Biblibre demo installation of Koha 16.11 ( 
http://intranet-demo.biblibre.com/ ) and it reported no more errors:

SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, i.barcode, 
v.lib, 
ExtractValue(bi.marcxml,'//datafield[@tag="205"]/subfield[@code="a"]') 
as Edition, 
ExtractValue(bi.marcxml,'//datafield[@tag="210"]/subfield[@code="c"]') 
as Publisher, 
ExtractValue(bi.marcxml,'//datafield[@tag="210"]/subfield[@code="d"]') 
as Date
FROM items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
LEFT JOIN biblioitems bi ON (i.biblioitemnumber=bi.biblioitemnumber)
LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value)
WHERE i.itemlost != 0 AND v.category='LOST'

In the demo insrtallation no matches were found but in your own database 
this would be different, I guess.

Hope this helps.

Best wishes: Michael
-- 
Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
T 0041 (0)61 261 55 61 · E mik at adminkuhn.ch · W www.adminkuhn.ch