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 <[email protected]>: > 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 <[email protected]> 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 >> [email protected] >> https://lists.katipo.co.nz/mailman/listinfo/koha >> > > _______________________________________________ Koha mailing list http://koha-community.org [email protected] https://lists.katipo.co.nz/mailman/listinfo/koha
