[Koha] A more complete report on lost documents

[vanda koha]

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 at 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 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
>>
>
>