[Koha] A more complete report on lost documents

[Eric Phetteplace]

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

5212 Broadway, Oakland, CA 94618

1111 8th St., San Francisco, CA 94107

Preferred Pronoun(s): he/him

:(){ :|: & };:

On Tue, Feb 27, 2018 at 2:03 AM, vanda koha <vandakoha at 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 at lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>