[Koha] A more complete report on lost documents

[vanda koha]

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