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@adminkuhn.ch · W www.adminkuhn.ch

Hi Eric, Good news! Your query had only a small problem: a comma on line 5: ExtractValue(bi.marcxml, '//datafield[@tag="210"]/subfield[@code="d"]') as Date*,* As soon as I removed the comma, it worked just fine! :) So I'll leave here the right query: it may be useful for someone else: 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 authorised_values v ON (i.itemlost=v.authorised_value) LEFT JOIN biblioitems bi ON (i.biblioitemnumber=bi.biblioitemnumber) WHERE i.itemlost != 0 AND v.category='LOST' Thanks a lot for your help and your explanation. Without it I wouldn't be able to find the answer. All the best, Vanda 2018-02-28 9:47 GMT+00:00 vanda koha <vandakoha@gmail.com>:

Hi Eric, Thanks for your help.

I tried your query but it didn't work. The Koha showed the following:

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

Any ideas?

Thanks again,

Vanda

2018-02-27 16:28 GMT+00:00 Eric Phetteplace <ephetteplace@cca.edu>:

...

Hi Vanda,

You need to connect to the biblioitems table which holds the MARC record in its "marcxml" field, then use the SQL ExtractValue <https://mariadb.com/kb/en/library/extractvalue/> function to pull out these specific MARC fields. This process is necessary because not every single MARC tag is in its own database field, you have to get most of the data out of the field that storess the full record. So a report like this should accomplish what you want:

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'

The three ExtractValue lines pull out the fields you specified while the one additional join adds the biblioitems table to the query.

Note that, in newer Koha versions starting with 17.05 the MARCXML record is stored in the biblio_metadata <http://schema.koha-community.org/17_05/tables/biblio_metadata.html> table and so the report would look just slightly different.

Best,

ERIC PHETTEPLACE

Systems Librarian

libraries.cca.edu | vault.cca.edu | 510.594.3660 <(510)%20594-3660>

5212 Broadway, Oakland, CA 94618 <https://maps.google.com/?q=5212+Broadway,+Oakland,+CA+94618&entry=gmail&source=g>

1111 8th St., San Francisco, CA <https://maps.google.com/?q=1111+8th+St.,+San+Francisco,+CA%C2%A0+94107&entry=gmail&source=g> 94107 <https://maps.google.com/?q=1111+8th+St.,+San+Francisco,+CA%C2%A0+94107&entry=gmail&source=g>

Preferred Pronoun(s): he/him

:(){ :|: & };:

On Tue, Feb 27, 2018 at 2:03 AM, vanda koha <vandakoha@gmail.com> wrote:

...

Hi everybody,

We are using 16.11.04 Koha version and I need to have a list of all my Lost documents, so I am running the following report:

SELECT i.itemnumber, b.title, b.author, i.itemcallnumber, i.barcode, v.lib FROM items i LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber) LEFT JOIN authorised_values v ON (i.itemlost=v.authorised_value) WHERE i.itemlost != 0 AND v.category='LOST'

However this is a little incomplete, because I need also the information about the *Edition Statement*, *Publisher Name *and the *Date of Publication *to appear in the results. We use Unimarc and the fields I also need to show up in the results are:

205 a (Edition Statement) 210 c (Publisher Name) 210 d (Date of Publication).

Can anyone help me with this SQL query?

Many thanks in advance

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